Texas Longhorn
Active Member
- Joined
- Sep 30, 2003
- Messages
- 493
Hi all,
I have a worksheet with several dozen columns of data. The first column contains dates, and I have a dynamically named range for this called DataDates which is defined as =OFFSET(Summary!$A$13,0,0,COUNT(Summary!$A:$A),1). I have manually created a few more named ranges in the Sheet; an example is FinMetric1 which is defined as =OFFSET(DataDates,0,COLUMNS(Summary!$A$12:$C$12)-1).
Rather than continue to manually name the remaining ~40 ranges, I would like to use VBA to automate the process. My plan (which I am failing to execute) is as follows: For each column for which I'd like to have a named range, I would type a "Name" field in row 9 and a "Refers To" formula in row 10 above the relevant data. For example, column X contains data that I want to name "FinMetric2." So in X9 I would have FinMetric2 and in X10 I would have OFFSET(DataDates,0,COLUMNS(Summary!$A$12:$X$12)-1). Column AA contains data I want to name "FinMetric3" so AA9 would contain FinMetric3 and AA10 would contain OFFSET(DataDates,0,COLUMNS(Summary!$A$12:$AA$12)-1). Etcetera.
My problem is that I do not know how to have VBA reference the contents in rows 9 and 10 when defining the names. Can anyone point me in the right direction?
Many thanks,
Bill
I have a worksheet with several dozen columns of data. The first column contains dates, and I have a dynamically named range for this called DataDates which is defined as =OFFSET(Summary!$A$13,0,0,COUNT(Summary!$A:$A),1). I have manually created a few more named ranges in the Sheet; an example is FinMetric1 which is defined as =OFFSET(DataDates,0,COLUMNS(Summary!$A$12:$C$12)-1).
Rather than continue to manually name the remaining ~40 ranges, I would like to use VBA to automate the process. My plan (which I am failing to execute) is as follows: For each column for which I'd like to have a named range, I would type a "Name" field in row 9 and a "Refers To" formula in row 10 above the relevant data. For example, column X contains data that I want to name "FinMetric2." So in X9 I would have FinMetric2 and in X10 I would have OFFSET(DataDates,0,COLUMNS(Summary!$A$12:$X$12)-1). Column AA contains data I want to name "FinMetric3" so AA9 would contain FinMetric3 and AA10 would contain OFFSET(DataDates,0,COLUMNS(Summary!$A$12:$AA$12)-1). Etcetera.
My problem is that I do not know how to have VBA reference the contents in rows 9 and 10 when defining the names. Can anyone point me in the right direction?
Many thanks,
Bill
