Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Range error - How does this work and what am I doing wrong? | Excel Programming | |||
Can I protect a range of worksheets in a book with one action? | Excel Worksheet Functions | |||
User input as a range. What am I doing wrong? | Excel Programming | |||
User input as a range. What am I doing wrong? | Excel Programming | |||
deleting wrong range! | Excel Programming |