I have serveral Name Ranges macros that start with the letter X.
I need to change all the cell references of those beginning with X to a variable input in cell X1.
For example: I go to Insert,Name, Define and it gives me the range reference at the bottom.
XUnits!$A$2:$A$25. What I need is.. if the Name Range Begins with an X, use the value in X1= (30)
as the last cordinate. 25 now becomes 30
It would loop and get all Name Ranges begining with X
Dynamically adjust row reference in specific named ranges using input cell
-
-
-
Re: Dynamically adjust row reference in specific named ranges using input cell
If you only have a few named ranges then wny not simply adjust the range formula to something like this? Why do you need a loop?
=INDIRECT("XUnits!$A$2:$A$" & XUnits!$X$1)
-
Re: Dynamically adjust row reference in specific named ranges using input cell
If you want a macro perhaps try
Code
Display MoreOption Explicit Sub MODIF_RANGE_ADDRESS() Dim NAME As String Dim ADDRESS As String Dim MyROW As Long Dim F As NAME On Error GoTo LAST_END For Each F In ActiveWorkbook.Names MyROW = Range("X1") ADDRESS = F.RefersTo ADDRESS = Right(ADDRESS, Len(ADDRESS) - 2) ' REMOVE =" ADDRESS = Left(ADDRESS, Len(ADDRESS) - 1) If (Left(ADDRESS, 1) = "X") Then ADDRESS = Left(ADDRESS, InStrRev(ADDRESS, "$")) & MyROW F.RefersTo = ADDRESS End If Next F LAST_END: End Sub
-
Re: Dynamically adjust row reference in specific named ranges using input cell
I created a range (A1:B5) and called it Xinput.
I ran the macro above and there was no change to my range, although it does work for XUser and works like I want it to but I have to go to Insert, Name, Define to find them.
When I select the range arrow to select a range, your Test1 and Test2 appear but Rg1 and Rg2 dont appear??
I go to Insert, Name,Define and they are there changed to the new range.
How do I get the name to appear in the range selection box ? -
Re: Dynamically adjust row reference in specific named ranges using input cell
This might work better for you. If not, then try commenting the On Error line, then run and see if you get a debug and what line it's on.
Code
Display MoreSub xRangeChange() Dim ADDRESS As String Dim MyROW As Long Dim F As NAME On Error GoTo LAST_END MyROW = Range("X1").Value For Each F In ActiveWorkbook.Names If Left(UCase(F.NAME), 1) = "X" Then ADDRESS = F.RefersTo ADDRESS = Left(ADDRESS, InStrRev(ADDRESS, "$")) & MyROW F.RefersTo = ADDRESS End If Next F LAST_END: End Sub
-
-
Re: Dynamically adjust row reference in specific named ranges using input cell
I stand and applaud your expertiese and time for helping me and others.
It works wonderfully.
Cheers -
Re: Dynamically adjust row reference in specific named ranges using input cell
Just two things I have to say.
1) helping here can produce thanks, utterly no thanks, and very nicely done thanks - yours would be among the last category. And it certainly does make a difference.
2) looking at things closely enough to sometimes be able to pitch in with help is one really fantastic way of acquiring new tricks. Until I saw PCI's response, I had no idea there was an InStrRev function - and wow is that handy or what!?
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!