Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-24-2022, 12:14 AM
soroush.kalantari soroush.kalantari is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2016
Novice
HOW TO write a macro for dynamic relative named ranges in Excel?
 
Join Date: Jun 2021
Posts: 27
soroush.kalantari is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-24-2022, 01:51 PM
NoSparks NoSparks is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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:
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)
the variables cannot be within quotes
Code:
"=INDEX(cellAddress02:cellAddress,myrow)"
'should be
"=INDEX(" & cellAddress02 & ":" & cellAddress & "," & myrow & ")"
Quote:
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)
use
Code:
cellAddress = cell.Parent.Name & "!" & cell.address(1,0)
Reply With Quote
  #3  
Old 01-24-2022, 09:27 PM
soroush.kalantari soroush.kalantari is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2016
Novice
HOW TO write a macro for dynamic relative named ranges in Excel?
 
Join Date: Jun 2021
Posts: 27
soroush.kalantari is on a distinguished road
Default

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".
Reply With Quote
  #4  
Old 01-25-2022, 02:00 AM
NoSparks NoSparks is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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
Reply With Quote
  #5  
Old 01-25-2022, 12:01 PM
NoSparks NoSparks is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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
Attached Files
File Type: xlsm Named_Ranges.xlsm (16.2 KB, 1 views)
Reply With Quote
  #6  
Old 01-25-2022, 10:41 PM
soroush.kalantari soroush.kalantari is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2016
Novice
HOW TO write a macro for dynamic relative named ranges in Excel?
 
Join Date: Jun 2021
Posts: 27
soroush.kalantari is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 01-26-2022, 07:04 AM
NoSparks NoSparks is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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
Reply With Quote
  #8  
Old 01-28-2022, 09:35 PM
soroush.kalantari soroush.kalantari is offline HOW TO write a macro for dynamic relative named ranges in Excel? Windows 10 HOW TO write a macro for dynamic relative named ranges in Excel? Office 2016
Novice
HOW TO write a macro for dynamic relative named ranges in Excel?
 
Join Date: Jun 2021
Posts: 27
soroush.kalantari is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
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
That was my fault. I presented my problem in wrong way. My question must be edites as follows : 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 column (for example, A1 is inflation and A1:f1 are .01,.02 …which are inflation amount of different years).
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.
Attached Files
File Type: xlsm namecopy.xlsm (21.1 KB, 1 views)
Reply With Quote
Reply

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
HOW TO write a macro for dynamic relative named ranges in Excel? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft