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

Try

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


--
__________________________________
HTH

Bob

"rstroughair" wrote in message
...
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)



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

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!

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:A1 00")

If this post helps click Yes
---------------
Jacob Skaria


"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