Application.Match problem

  • Thread starter Thread starter rstroughair
  • Start date Start date
R

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
 
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)
 
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
 
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 -
Click to expand...

Thanks for your help guys. Got it working now!
 
Back
Top