Is there a way to have variable ranges for worksheet functions in VBA? I've tried it, but I always get an error. For example, if I use:
Sheet4.Cells(x + 1, y + 1) = Application.WorksheetFunction.VLookup(Sheet2.Cells(x, y).Value, Sheet1.Range("A2:B50"), 2, False)
the code works. However, if I use:
Sheet4.Cells(x + 1, y + 1) = Application.WorksheetFunction.VLookup(Sheet2.Cells(x, y).Value, Sheet1.Range(cells(x,y),cells(x+10,y+10)), 2, False)
I get an error. Any ideas are much appreciated!