CODEWITHSUNDEEP
excelvba
ss_0708
ss_0708

Reputation: 193

Vlookup in filtered Range with Varaible Lookup Value, Variable Lookup Range VBA

I am trying to apply Vlookup on a filtered range with Variable Lookup Value(Changing according to the row number) and Variable Lookup Range(From a user browsed workbook). But, the formula bar after running the code shows the formula as :-

=IFERROR(VLOOKUP(@Sri Lanka15-@a_One-@Time Base Rent,'[C_Rent Report_25082020.xlsx]Sheet 1'!$J$1:$N$968,4,0)," ")

I am not sure where these "@" signs are coming from. The lookup value for this particular row is :-Sri Lanka15-a_One-Time Base Rent.

Below is the code:-

    Dim LR As Long   ' Defined as Last row in source file
    Dim nlr As Long   'Defined as Last row in Macro Workbook where vlookup is applied
    Dim Filename As String
    Filename = Application.GetOpenFilename(FileFilter:="All Files(*.xls; *.xlsx; *.csv),*xls,*.xlsx, *csv", Title:="Select File To Be Opened")
    Workbooks.Open Filename:=Filename
    sourcefile = Dir(Filename)
    With ActiveSheet
       Range("A1:AQ" & nlr).AutoFilter Field:=25, Criteria1:="One-Time Base Rent"
       For Each cell In Range("AA2:AA" & nlr).SpecialCells(xlCellTypeVisible)
            lookupvalue = Cells(cell.Row, "Z").Value
            cell.Formula = "=IFERROR(VLOOKUP(" & lookupvalue & ",'[" & sourcefile & "]Sheet 1'!$J$1:$N$" & LR & ",4,0),"" "")"    ' The problem seems to be here in lookup value as rest are appearing as fine in formula
       Next
             
    End With

Since i need to apply subsequent filters after this. i would like to keep the lookup value as variable.

I have tried WorksheetFunction.Vlookup too, but i am not sure how to define the range from a file chosen by user in worksheetfunction

Any help is highly appreciated !! Thanks

Upvotes: 0

Views: 93

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try replacing of

cell.Formula = "=IFERROR(VLOOKUP(" & lookupvalue & ",'[" & sourcefile & "]Sheet 1'!$J$1:$N$" & LR & ",4,0),"" "")"

with

cell.Formula = "=IFERROR(VLOOKUP(" & cells(cell.Row, "Z").Address & ",'[" & sourceFile & "]Sheet 1'!$J$1:$N$" & lr & ",4,0),"" "")"

Upvotes: 1

Related Questions