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

Select allOpen in new window

Stock-Point-Inventory.xlsx