VBA Using Vlookup to assign Value to ActiveCell, in another referenced workbook

Question

I've been trying to use vlookup in VBA and it's being quite annoying.

ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(currentrow, 12), currwor.Worksheets("Sheet1").Range("R4C3:R160C10"), 6, False)

is the code

The main elements I've used are currwor which has been set to reference another workbook and I'm looking to use vlookup on the range in there.

However it keeps kicking up an error saying subscript out of range.

I suspect currwor is the guilty party in this Set currwor = ActiveWorkbook (then the workbook in which the above is executed is opened, and that is assigned a workbook name as well.) I can't use a specific range because I'm trying to perform the vlookup on each cell in a range, and that range changes everytime, and the currwor is to be executed on a particular document I get but the name may vary (format however remains the same.)

Any suggestions on it?

edit: One error is that it's not supposed to be sheet1 but 'Stock Report by Product'. However it's still kicking up the subscript out of range error.

Answer 1

by using Set currwor = ActiveWorkbook you assign currently active workbook to currwor however you mentioned prior to that it should be a different workbook? meaning you should have a line Set currworb = Workbooks("desired workbook name")?

Answer 2

Yes - Idiotic mistake. As pointed out the sheet name was incorrect and the problem was that in execution I skipped the bit where the worksheet was assigned to currwor.

Idiotic enough that I should consider deleting this post off here.