Range in wrong book
 

Hi

I am using code to loop through all the files in a folder, read some data,
write back to the book containing the code and move on to the next book.

Part of my code has the following line:
m = Application.VLookup(Chr(ce.Column + 64), Range("month"), 2, 0)

Trouble is the named range is not in the book being read hence it doesn't
work. Is there an eaasier way other than copy the named range to every
workbook being read?

Range in wrong book
 

Hi

Possibly you could store the address of the named range in a string
and then use that:

Dim strAddress as String
strAddress =
Split(Workbooks("YourBookWithTheNamedRange").Names ("month").RefersTo,"!")
(1)
m = Application.VLookup(Chr(ce.Column + 64), Range(strAddress), 2, 0)

Hope this helps!

Richard


On 8 Feb, 08:51, Newbie wrote:
Hi

I am using code to loop through all the files in a folder, read some data,
write back to the book containing the code and move on to the next book.

Part of my code has the following line:
m = Application.VLookup(Chr(ce.Column + 64), Range("month"), 2, 0)

Trouble is the named range is not in the book being read hence it doesn't
work. Is there an eaasier way other than copy the named range to every
workbook being read?

Range in wrong book
 

You could use a Workbook.Range reference rather than the default
ActiveWorkbook.Range

m = Application.VLookup(Chr(ce.Column + 64), Workbooks("Workbook containing
range.xls").Range("month"), 2, 0)

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Newbie" wrote in message
...
Hi

I am using code to loop through all the files in a folder, read some data,
write back to the book containing the code and move on to the next book.

Part of my code has the following line:
m = Application.VLookup(Chr(ce.Column + 64), Range("month"), 2, 0)

Trouble is the named range is not in the book being read hence it doesn't
work. Is there an eaasier way other than copy the named range to every
workbook being read?

Range in wrong book
 

I was thinking on the same lines but...

m = Application.VLookup(Chr(ce.Column + 64),
Workbooks("Driver1.xls").Range("month"), 2, 0)

gives me a runtime error 438
object doesn't support property or methd



"Charles Williams" wrote:

You could use a Workbook.Range reference rather than the default
ActiveWorkbook.Range

m = Application.VLookup(Chr(ce.Column + 64), Workbooks("Workbook containing
range.xls").Range("month"), 2, 0)

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Newbie" wrote in message
...
Hi

I am using code to loop through all the files in a folder, read some data,
write back to the book containing the code and move on to the next book.

Part of my code has the following line:
m = Application.VLookup(Chr(ce.Column + 64), Range("month"), 2, 0)

Trouble is the named range is not in the book being read hence it doesn't
work. Is there an eaasier way other than copy the named range to every
workbook being read?

Range in wrong book
 

Dim rng as Range, rng1 as Range
set rng = Workbooks("Driver1.xls").Name("Month").ReferstoRan ge

m = Application.VLookup(Chr(ce.Column + 64), _
rng, 2, 0)

If you want to look in the same area in the activesheet instead of in
Driver1.xls then

Dim rng as Range
set rng = Workbooks("Driver1.xls").Name("Month").ReferstoRan ge
set rng1 = ActiveSheet.Range(rng.Address)
m = Application.VLookup(Chr(ce.Column + 64), _
rng1, 2, 0)


this assumes the named range Month is a singe contiguous block of cells.

--
Regards,
Tom Ogilvy


"Newbie" wrote:

I was thinking on the same lines but...

m = Application.VLookup(Chr(ce.Column + 64),
Workbooks("Driver1.xls").Range("month"), 2, 0)

gives me a runtime error 438
object doesn't support property or methd



"Charles Williams" wrote:

You could use a Workbook.Range reference rather than the default
ActiveWorkbook.Range

m = Application.VLookup(Chr(ce.Column + 64), Workbooks("Workbook containing
range.xls").Range("month"), 2, 0)

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Newbie" wrote in message
...
Hi

I am using code to loop through all the files in a folder, read some data,
write back to the book containing the code and move on to the next book.

Part of my code has the following line:
m = Application.VLookup(Chr(ce.Column + 64), Range("month"), 2, 0)

Trouble is the named range is not in the book being read hence it doesn't
work. Is there an eaasier way other than copy the named range to every
workbook being read?

Range in wrong book
 

As usual Tom, you have come up with the goods.

I hope nice things happen to you.

Thanks.



"Tom Ogilvy" wrote:


Dim rng as Range, rng1 as Range
set rng = Workbooks("Driver1.xls").Name("Month").ReferstoRan ge

m = Application.VLookup(Chr(ce.Column + 64), _
rng, 2, 0)

If you want to look in the same area in the activesheet instead of in
Driver1.xls then

Dim rng as Range
set rng = Workbooks("Driver1.xls").Name("Month").ReferstoRan ge
set rng1 = ActiveSheet.Range(rng.Address)
m = Application.VLookup(Chr(ce.Column + 64), _
rng1, 2, 0)


this assumes the named range Month is a singe contiguous block of cells.

--
Regards,
Tom Ogilvy


"Newbie" wrote:

I was thinking on the same lines but...

m = Application.VLookup(Chr(ce.Column + 64),
Workbooks("Driver1.xls").Range("month"), 2, 0)

gives me a runtime error 438
object doesn't support property or methd



"Charles Williams" wrote:

You could use a Workbook.Range reference rather than the default
ActiveWorkbook.Range

m = Application.VLookup(Chr(ce.Column + 64), Workbooks("Workbook containing
range.xls").Range("month"), 2, 0)

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Newbie" wrote in message
...
Hi

I am using code to loop through all the files in a folder, read some data,
write back to the book containing the code and move on to the next book.

Part of my code has the following line:
m = Application.VLookup(Chr(ce.Column + 64), Range("month"), 2, 0)

Trouble is the named range is not in the book being read hence it doesn't
work. Is there an eaasier way other than copy the named range to every
workbook being read?