#1
|
|||
|
|||
HOW TO write a macro for dynamic relative named ranges in Excel?
In my workbook, I have many rows in different sheets. In the first cell of each row(column A), there is a name (header) and in the other cells there are some numbers relating to first row (for example, A1 is inflation and A2:A10 are .01,.02 …which are inflation amount of different years). For making my formula more readable, I want to use dynamic relative named ranges. For automating this process, I have written following macro. There are 2 problem with this macro:
1.VBA consider parameters of my macro as text not value (for example, VBA considers cellAddress02 as “cellAddress02” not $A$1 which is what I expect) 2.For making my named ranges relative rather than absolute, I need a VBA function to give a cell address which doesn't have $ signs before the letter (for example, I want A$1 not $A$1) Can anyone help me on these problems? Sub Macro3() 'Dim rng As Range Dim n As Integer Dim myrow As Integer Dim cell As Range Dim cell02 As Range Dim cellAddress As String Dim cellAddress02 As String myname = ActiveCell.Value myrow = ActiveCell.Row Set cell = ActiveCell cellAddress = cell.Parent.Name & "!" & cell.address(External:=False) Set cell02 = Range("A1") cellAddress02 = cell02.Parent.Name & "!" & cell02.address(External:=False) ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersToR1C1:= _ "=INDEX(cellAddress02:cellAddress,myrow)" End Sub |
#2
|
|||
|
|||
Not sure about what you're doing or how you're going about it,
but for the answers to the questions you've asked Quote:
Code:
"=INDEX(cellAddress02:cellAddress,myrow)" 'should be "=INDEX(" & cellAddress02 & ":" & cellAddress & "," & myrow & ")" Quote:
Code:
cellAddress = cell.Parent.Name & "!" & cell.address(1,0) |
#3
|
|||
|
|||
Thank you NoSparks for your constructive response. My second problem was solved, but when I replace "=INDEX(cellAddress02:cellAddress,myrow)" with "=INDEX(" & cellAddress02 & ":" & cellAddress & "," & myrow & ")", the code encounters runtime error 1004, saying "there is a problem with this formula".
|
#4
|
|||
|
|||
I only re-wrote what you already had so that vba would recognize the variables
You made up the formula When I create the dynamic range manually the Refers to formula is =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$ A)) (There is no space before the last A the forum software is inserting it) Last edited by NoSparks; 01-25-2022 at 10:08 AM. Reason: added manually created info |
#5
|
|||
|
|||
Here's a macro that will build that formula and add the named range
Code:
Sub Create_Dynamic_Range() Dim RngName As String, ShtName As String, StartAddress As String Dim RngCol As String, ReferString As String RngName = ActiveCell.Value ShtName = ActiveCell.Parent.Name & "!" RngCol = "$" & Split(Mid(ActiveCell.Address, 2), "$")(0) StartAddress = ActiveCell.Offset(1).Address ReferString = "=" & ShtName & StartAddress & ":INDEX(" & ShtName & RngCol & ":" & _ RngCol & ",COUNTA(" & ShtName & RngCol & ":" & RngCol & "))" ActiveWorkbook.Names.Add Name:=RngName, RefersTo:=ReferString End Sub |
#6
|
|||
|
|||
Thank you very much. Inspired with your response, I modified my code as follow and that solved my problem.
Sub dynamicrange() Dim rng As Range Dim n As Integer Dim myrow As Integer Dim cell As Range Dim cell02 As Range Dim cellAddress As String Dim cellAddress02 As String myname = ActiveCell.Value myrow = ActiveCell.Row Set cell = ActiveCell cellAddress = cell.Parent.Name & "!" & cell.address(1, 0) Set cell02 = Range("A1") cellAddress02 = cell02.Parent.Name & "!" & cell02.address(1, 0) ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersTo:="=" & "INDEX(" & cellAddress02 & ":" & cellAddress & "," & myrow & ")" End Sub |
#7
|
|||
|
|||
I must confess, I have no idea how or why that works.
I don't know where the active cell is when you start and I guess your sheet lay out is not as I envision it, but hey, if you're happy, I'm happy. Good Luck with your project. NoSparks |
#8
|
|||
|
|||
Quote:
Also there was a minor problem with my codes that I solved it. (The line ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersTo:="=" & "INDEX(" & cellAddress02 & ":" & cellAddress & "," & myrow & ") must be replaced with ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersTo:="=" & "INDEX(" & cellAddress02 & ":" & cellAddress & "," & "row(" & cellAddress & ")" & ")". (sorry, the Edit Bottom is not available for me to edit them) But anyway, my main problem was how to make VBA codes distinguish between text and parameter which I learned form your response. |
Tags |
address, macro, relative named ranges |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Use of named ranges based on cell content | Intruder | Excel | 12 | 02-25-2019 09:42 AM |
Named Ranges Help | SavGDK | Excel | 5 | 05-01-2017 09:41 AM |
How to use named ranges in excel vba? | bosve73 | Excel Programming | 4 | 01-25-2012 09:26 AM |
Dynamic Named Ranges using text | hannu | Excel | 0 | 06-22-2010 04:42 PM |
Can't import home adresses in outlook 2010 from excel named ranges | eekie | Outlook | 0 | 05-14-2010 02:04 PM |