VLookup: Range with variable

[dabon]

Hi,

I'm not understanding why the code below does not work. The error shown is related to the Range. The message is: Run-time error '1004': Method 'Range' of object'_Global' failed.


I'm using two different workbook:

• Workbook_1 where macro is run and where there is the data;
• Workbook_2 where there is the value assigne to the data that I want to find.

Since there are many data in Workbook_1 that I want to search in Workbook_2, I'm using a variable into the range. In bold where the problem is.

Dim a As Worksheet
Set a = Worksheets("Sheet1")
Dim search As String

r_workbook1 = 1
c_workbook1 = 1

While(a.cells(r_workbook1,c_workbook1).value <>"")
search = a.Cells(r_workbook1, c_workbook1).Value ' What should be find into Workbook_2
a.Cells(r_workbook1, c_workbook1 + 2).Value = Application.WorksheetFunction.VLookup(Range("a.cells(r_workbook1,c_workbook1)"), Workbooks("Workbook_2.xlsx").Sheets("Sheet1").Range("B2:E10"), 3, False)
r_workbook1=r_workbook1+1
Wend

Obvioulsy, the VLookup works fine in case of Range is uses e.g. (Range("A2")).

Thanks

 

[dabon]

Hi,

I'm not understanding why the code below does not work. The error shown is related to the Range. The message is: Run-time error '1004': Method 'Range' of object'_Global' failed.


I'm using two different workbook:

• Workbook_1 where macro is run and where there is the data;
• Workbook_2 where there is the value assigne to the data that I want to find.

Since there are many data in Workbook_1 that I want to search in Workbook_2, I'm using a variable into the range. In bold where the problem is.

Dim a As Worksheet
Set a = Worksheets("Sheet1")
Dim search As String

r_workbook1 = 1
c_workbook1 = 1

While(a.cells(r_workbook1,c_workbook1).value <>"")
search = a.Cells(r_workbook1, c_workbook1).Value ' What should be find into Workbook_2
a.Cells(r_workbook1, c_workbook1 + 2).Value = Application.WorksheetFunction.VLookup(Range("a.cells(r_workbook1,c_workbook1)"), Workbooks("Workbook_2.xlsx").Sheets("Sheet1").Range("B2:E10"), 3, False)
r_workbook1=r_workbook1+1
Wend

Obvioulsy, the VLookup works fine in case of Range is uses e.g. (Range("A2")).

Thanks

Sorry I forgot to say that code does not work also in case of

a.Cells(r_workbook1, c_workbook1 + 2).Value = Application.WorksheetFunction.VLookup(Range("search"), Workbooks("Workbook_2.xlsx").Sheets("Sheet1").Range("B2:E10"), 3, False)

that is my main idea and I want to use.

Thanks