Application.Match problem

[rstroughair]

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

 

[Bob Phillips]

Try

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

 

[Dave Peterson]

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)

 

[Jacob Skaria]

If you have declared the variables correctly you dont need to worry; but are
you sure the issue is around that

Dim strWBook as String
Dim strWSheet as String

strWBook = "Book1"
strWSheet = "Sheet1"

Set rngTemp = Workbooks(strWBook).Sheets(strWSheet).Range("A2:A100")

If this post helps click Yes

 

[rstroughair]

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)
















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for your help guys. Got it working now!