Convert Excel VBA Range for Use in Excel Function

0
votes

I want to place a VLOOKUP formula into an Excel cell from within a VBA macro. The range to be used as the VLOOKUP's second argument is identified in the macro as a Range object. The range is two columns wide but a variable number of rows. Here's the macro code I have that obviously throws an Excel error:

Dim processedRow As Integer
Dim rngVlookupData As Range
Dim ws As Worksheet
... code ...
Set ws = ThisWorkbook.Sheets("HR Eval Report")
ws.Range("BC8", "BBD8").Select
ws.Range(Selection, Selection.End(xlDown)).Select
Set rngVlookupData = Selection
Range(Cells(processedRow, 26), Cells(processedRow, 26)) = "=IF(VLOOKUP(Z7,rngVlookupData,1)=Z7,""Yes"","""")"
Range("AA5") = "=IF(VLOOKUP(Z7,rngVlookupData,1)=Z7,VLOOKUP(Z7,rngVlookupData,2),"""")"

Obviously, Excel doesn't recognize "rngVlookupData", which causes a #NAME error. Any help is much appreciated.

1
excelvba

1 Answers

0
votes

That is because it is being treated as a string. You this

Range("AA5").Formula = "=IF(VLOOKUP(Z7," & _
                       rngVlookupData.Address & _
                       ",1)=Z7,VLOOKUP(Z7," & _
                       rngVlookupData.Address & _
                       ",2),"""")"`