Range in wrong book

[Guest]

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?

 

[RichardSchollar]

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

 

[Charles Williams]

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

 

[Guest]

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

 

[Guest]

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

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").ReferstoRange
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.

 

[Guest]

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

I hope nice things happen to you.

Thanks.