On 27 Apr, 13:14, Dave Peterson wrote:
First the workbook already has to be open.

Second, the workbooks collection does not include the path--just the filename.

Third, if S were declared as a string, then I think that this would work:

Workbooks(f).Sheets(s).Range(r)

But since you didn't share how s was declared, this will work ok:

Workbooks(f).Sheets(cstr(s)).Range(r)





rstroughair wrote:

Good Morning,

I am attempting to use the worksheet function match within some VBA
but am encountering a problem.

Basically I am trying to construct the lookup range by stringing
together four elements - p (the path), f (the file), s (the sheet
name) and r (the range).

The problem I am having I believe is related to the sheet name. I am
taking this value from a cell in a range which I am looping through. I
can see when debugging that the VBA is picking up the correct value
but for some reason the lookup is providing a #value error.

I think the problem is because the sheet names are all numbers (they
are actually days in the month in the format 1, 2, 3, ... , 30, 31).

Can someone please provide an example of how I can create such an
argument?

I have tried variations of the following with no success:-

Workbooks(p & "\" & f).Sheets(s).Range(r)

where p, f and r are hard coded. Hence I believe the problem is
because Sheets(s) is being interpreted as Sheets(1) rather than Sheets
("1") and so forth.

Thanks in advance,

Richard

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for your help guys. Got it working now!