Need Help with VLookup in VBA

Thank you for looking at my question,

I am trying to execute a VLOOKUP() function in VBA but when it is transposed there are extra brackets in the statement that change it's result completely.


The idea is to compare a concatenation of cells in columns A and D with the contents of .Sheet("Stock Point Inventory") with the values in cells in column D of .Sheets("Fixed Locations")


The Excel function I am trying to replicate is
=IF(ISERROR(VLOOKUP(CONCATENATE(A2,"-",D2),'Fixed Locations'!D:D,1,0)),"NOK","OK")


In VBA I have written the function as:
strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!D:D,1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"


When the routine runs the text I expected to see in the formula bar is
=IF(ISNA(VLOOKUP(CONCATENATE(A1336,"-",D1336),'Fixed Locations'!D:D,1,0)),"NOK","OK")


But what I am seeing is
=IF(ISNA(VLOOKUP(CONCATENATE(A1339,"-",D1339),'Fixed Locations'!D:(D),1,0)),"NOK","OK") - note the extra set of brackets in 'Fixed Locations'!D:(D),

Can anybody tell me please why this is happening and how to correct it?


The code I use is
Sub Process_VLookup()
    
    strQuote = Chr(34)
    
    '' Filename
    strPath_SPI = "C:\Stock Point Inventory\Stock Point Inventory.xlsx"
    
    Set wbSPI = Application.Workbooks.Open(strPath_SPI)
    
    '' Get No Rows in Worksheet
    lngSPI_TotalRows = wbSPI.Worksheets("Stock Point Inventory").Cells(65536, "A").End(xlUp).Row
    
    MsgBox "Total Rows: " & lngSPI_TotalRows, vbOKOnly
    
    '' Function String
    strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!D2:D" & lngSPI_TotalRows & ",1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"
    
    
    With wbSPI.Sheets("Stock Point Inventory")
    
        lngSPI_Row = .Range("J2").Row
        lngSPI_Col = .Range("J2").Column
        
        '' Range is all cells in Col J
        Set rngRange = Range("J2:J" & lngSPI_TotalRows)
        
        '' Apply function to each in cell in col J
        For Each rngCell In rngRange
            .Cells(lngSPI_Row, lngSPI_Col).FormulaR1C1 = strVLookup
            lngSPI_Row = lngSPI_Row + 1
        Next rngCell
        
        '' Apply conditional formatting
        .Cells.FormatConditions.Delete
    
        With .Range("A2:J" & lngSPI_TotalRows).FormatConditions.Add(Type:=xlExpression, Formula1:="=INDIRECT(""J"" & ROW())=""NOK""")
            .SetFirstPriority
            .Font.Color = vbRed
            .StopIfTrue = True
        End With
         
        '' Sort Data With NOK's at the top of the list
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("J2:J" & lngSPI_TotalRows), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
            
        .Sort.SortFields.Add Key:=Range("A2:A" & lngSPI_TotalRows), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
            
        With wbSPI.Sheets("Stock Point Inventory").Sort
            .SetRange Range("A1:J" & lngSPI_TotalRows)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End With

End Sub

Open in new window

Stock-Point-Inventory.xlsx
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
---
When the routine runs the text I expected to see in the formula bar is
=IF(ISNA(VLOOKUP(CONCATENATE(A1336,"-",D1336),'Fixed Locations'!D:D,1,0)),"NOK","OK")


But what I am seeing is
=IF(ISNA(VLOOKUP(CONCATENATE(A1339,"-",D1339),'Fixed Locations'!D:(D),1,0)),"NOK","OK") - note the extra set of brackets in 'Fixed Locations'!D:(D),
--

Are the row numbers changing too, or was that just a copy'n'paste issue with your question text?
Rgonzo1971Commented:
Hi,

pls try

 strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!C[-6]" & lngSPI_TotalRows & ",1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"

Open in new window

EDIT you cannot use R1C1 and absolute reference in the same formula

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Also, I see you are attempting to change 'Fixed Locations'!D:D to the extent of the data in column [D] (donated by the variable lngSPI_TotalRows).

---
lngSPI_TotalRows = wbSPI.Worksheets("Stock Point Inventory").Cells(65536, "A").End(xlUp).Row

 strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!D2:D" & lngSPI_TotalRows & ",1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"
---

This is the cause of your problem, I think.

I would be tempted to use this simple change:

From...

.Cells(lngSPI_Row, lngSPI_Col).FormulaR1C1 = strVLookup

To...

.Cells(lngSPI_Row, lngSPI_Col).FormulaR1C1 = Replace("=IF(ISNA(VLOOKUP(CONCATENATE(A$1,""-"",D$1),'Fixed Locations'!D:D,1,0)),""NOK"",""OK"")", "$1", rngCell.Row)

That may make the code easier to read/maintain.


If you do wish to restrict the usage of column [D], I would suggest not assuming the worksheet has 65,536 rows either.

Change:
lngSPI_TotalRows = wbSPI.Worksheets("Stock Point Inventory").Cells(65536, "A").End(xlUp).Row

To:
lngSPI_TotalRows = wbSPI.Worksheets("Stock Point Inventory").Cells(Rows.Count, "A").End(xlUp).Row
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try change:

strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!D2:D" & lngSPI_TotalRows & ",1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"

to:

strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9],""-"",RC[-6]),'Fixed Locations'!C[-6],1,0)),""NOK"",""OK"")"
Rgonzo1971Commented:
Hi,

instead of
strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!D2:D" & lngSPI_TotalRows & ",1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"
    
    
    With wbSPI.Sheets("Stock Point Inventory")
    
        lngSPI_Row = .Range("J2").Row
        lngSPI_Col = .Range("J2").Column
        
        '' Range is all cells in Col J
        Set rngRange = Range("J2:J" & lngSPI_TotalRows)
        
        '' Apply function to each in cell in col J
        For Each rngCell In rngRange
            .Cells(lngSPI_Row, lngSPI_Col).FormulaR1C1 = strVLookup
            lngSPI_Row = lngSPI_Row + 1
        Next rngCell

Open in new window

You could use

    strVLookup = "=IF(ISNA(VLOOKUP(CONCATENATE(RC[-9]," & strQuote & "-" & strQuote & ",RC[-6]),'Fixed Locations'!C[-6]" & lngSPI_TotalRows & ",1,0))," & strQuote & "NOK" & strQuote & "," & strQuote & "OK" & strQuote & ")"
    
    
    With Sheets("Stock Point Inventory")
    
        lngSPI_Row = .Range("J2").Row
        lngSPI_Col = .Range("J2").Column
        Range("J2").FormulaR1C1 = strVLookup
        '' Range is all cells in Col J
        Set rngRange = Range("J2:J" & lngSPI_TotalRows)
        Range("J2").AutoFill rngRange

Open in new window

Gary CroxfordOperations Support AnalystAuthor Commented:
RGonzo1971 Thank you, works a treat
[ fanpages ]IT Services ConsultantCommented:
You're welcome.

Oh.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.