Relative Column Absolute Row Code

SewStage

Board Regular
Joined
Mar 16, 2021
Messages
70
Office Version
  1. 365
Platform
  1. Windows
Hopefully a quick and easy solution: I have the following code that automatically creates defined names within a certain tab. It works great, but Name Manager continues to show it as $D$56 for instance and I need it to be D$56. I've searched Mr Excel and and the internet but am not a VBA expert at all, so I'm not sure of the correct syntax or where to put it within this code...?

VBA Code:
Public Sub subCreateNamedRanges()
' https://www.mrexcel.com/board/threads/autofill-naming-defined-names.1234019/
Dim Ws As Worksheet
Dim strMsg As String
Dim rngRangeList As Range
Dim Rng As Range
Dim s As String
Dim NamedRange As Name
Dim strName As String
Dim blnSheet As Boolean
Dim rngAddress  As Range
Dim intRow As Integer
Dim strColumns As String
Dim strCodes As String
Dim i As Integer
Dim arrColumns() As String
Dim arrCodes() As String
Dim WsList As Worksheet
Dim intCount As Integer

    ActiveWorkbook.Save
    
    strMsg = "Do you want to set the named ranges for the '" & ActiveSheet.Name & "' worksheet?"
    
    If MsgBox(strMsg, vbYesNo, "Security Question") = vbNo Then
        MsgBox "Activate the correct sheet before you run this code.", vbOKOnly, "Information"
        Exit Sub
    End If
    
    Set Ws = ActiveSheet
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("NamedRangeList1234019").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "NamedRangeList1234019"
    Set WsList = ActiveSheet
        
    WsList.Range("A2:F20000").Cells.ClearContents
    
    Ws.Activate
        
    strColumns = "D"
    arrColumns = Split(strColumns, ",")
    
    strCodes = Replace("PCBMO", " ", "", 1)
        
    arrCodes = Split(strCodes, ",")
    
    For i = LBound(arrColumns) To UBound(arrColumns)
                    
        For intRow = 1 To 25
        
            strName = arrCodes(i) & intRow
                    
            Set rngAddress = Ws.Cells(55 + intRow, Range(Trim(arrColumns(i)) & "1").Column)
                        
            With WsList
                .Range("A" & Rows.Count).End(xlUp)(2) = strName
                .Range("B" & Rows.Count).End(xlUp)(2) = "'" & Ws.Name & "!" & rngAddress.Address
            End With
            
            ThisWorkbook.Names.Add Name:=strName, RefersTo:=rngAddress
            
            intCount = intCount + 1
            
        Next intRow
        
    Next i
    
    ActiveWorkbook.Save
    
    WsList.Activate
    
    With WsList.Range("A1").CurrentRegion
    
        .Font.Size = 16
        .Font.Name = "Arial"
        .EntireColumn.AutoFit
        .VerticalAlignment = xlCenter
        With .Rows(1)
            .Value = Array("Name", "Address")
            .Font.Bold = True
            .Interior.Color = RGB(219, 219, 219)
        End With
        .RowHeight = 28
        With .Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = vbBlack
        End With
        .IndentLevel = 1
        
    End With
    
    WsList.Range("A2").Select
    ActiveWindow.FreezePanes = True
    
    strMsg = intCount & " named ranges have been created."
    strMsg = strMsg & vbCrLf & "These have been listed in the " & WsList.Name & " worksheet."
    
    MsgBox strMsg, vbInformation, "Confirmation"
            
End Sub
 

When the Range.Address property is used with the 'External' parameter, it returns the workbook name in square brackets. I did not want that part, so Split(rngAddress.Address(1, 0, , 1), "]")(1) is a way to remove that portion. The best way to understand the split function is to experiment with it.
VBA Code:
Sub TmpA()
    Dim a, b, c, d
    a = Range("A1:A100").Address(External:=True)
    b = Replace(a, "[", "]")
    c = Split(b, "]")
   
    Debug.Print vbCr & ActiveWorkbook.Name
   
    Debug.Print a
    Debug.Print c(0) & c(UBound(c)) & vbCr
End Sub
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

When the Range.Address property is used with the 'External' parameter, it returns the workbook name in square brackets. I did not want that part, so Split(rngAddress.Address(1, 0, , 1), "]")(1) is a way to remove that portion. The best way to understand the split function is to experiment with it.
VBA Code:
Sub TmpA()
    Dim a, b, c, d
    a = Range("A1:A100").Address(External:=True)
    b = Replace(a, "[", "]")
    c = Split(b, "]")
  
    Debug.Print vbCr & ActiveWorkbook.Name
  
    Debug.Print a
    Debug.Print c(0) & c(UBound(c)) & vbCr
End Sub
Thank you so much! My VBA For Excel For Dummies book arrived today, so I'm hoping I can better comprehend what all these various statements mean; and hopefully have to come out here less often begging for help. ;) Well *I* certainly don't have any other ideas for making this work.
 
Upvote 0
See if this works for you:
It assumes you want the name on the activesheet and relating to the activesheet.

VBA Code:
Sub RelativeRangeName()

    Dim Ws As Worksheet
    Dim rngAddress As Range
    Dim strName As String
    
    Set Ws = ActiveSheet
    Set rngAddress = Range("D56")
    strName = "Test6"
    Cells(2, rngAddress.Column).Select          ' Name is created relative to selected cell
    
    ThisWorkbook.Names.Add Name:=strName, RefersToR1C1:="=" & rngAddress.Address(1, 0, xlR1C1, False, rngAddress)

End Sub
 
Upvote 0
See if this works for you:
It assumes you want the name on the activesheet and relating to the activesheet.

VBA Code:
Sub RelativeRangeName()

    Dim Ws As Worksheet
    Dim rngAddress As Range
    Dim strName As String
   
    Set Ws = ActiveSheet
    Set rngAddress = Range("D56")
    strName = "Test6"
    Cells(2, rngAddress.Column).Select          ' Name is created relative to selected cell
   
    ThisWorkbook.Names.Add Name:=strName, RefersToR1C1:="=" & rngAddress.Address(1, 0, xlR1C1, False, rngAddress)

End Sub
Thank you Alex! Unfortunately, it didn't seem to actually do anything; I made sure I was on the applicable tab and verified in Name Manager that it still shows as $D$56 PCBMO1 after running it. I know next to nothing about VBA - I copied/pasted into a new/standalone module; should I have inserted this into my existing code?
 
Upvote 0
See if this works for you:
It assumes you want the name on the activesheet and relating to the activesheet.

VBA Code:
Sub RelativeRangeName()

    Dim Ws As Worksheet
    Dim rngAddress As Range
    Dim strName As String
   
    Set Ws = ActiveSheet
    Set rngAddress = Range("D56")
    strName = "Test6"
    Cells(2, rngAddress.Column).Select          ' Name is created relative to selected cell
   
    ThisWorkbook.Names.Add Name:=strName, RefersToR1C1:="=" & rngAddress.Address(1, 0, xlR1C1, False, rngAddress)

End Sub
Forget my last response - it worked!! But now it needs to be applied to all 25 rows (56-80) - Test6, Test7, Test8, etc. Is that a simple fix??
 
Upvote 0
Do you want us to just add a loop to the code I provided or did you or did you use you put it into your existing code in which case post your existing code so we add to that.

I can't work on it until later today though (Sydney Aust time). Maybe @rlv01 can assist to work with you now.
 
Upvote 0
Do you want us to just add a loop to the code I provided or did you or did you use you put it into your existing code in which case post your existing code so we add to that.

I can't work on it until later today though (Sydney Aust time). Maybe @rlv01 can assist to work with you now.
Oh I'd much prefer to work with the code you provided! The code I included in my original post here was a copy/paste of a different macro that really isn't at all needed for this one. Whenever you can get to it is fine with me; please know how much I appreciate your help!
 
Upvote 0
There are obviously going to be different ways of doing this, especially when it comes to giving the Named Range a name.
I have removed the Select it is not required after all, with the reference parameter in the address property handling that.
Since the column is relative you will need to be in Column D (active cell in Column D) if you want to see it show D in the RefersTo field in the Name manager.
Name manager will show whatever column you are in at the time.

Note: The names created will have the ActiveSheet hard coded in the name, so it matters which sheet is active at the time.
(There is a way of making it work on any sheet by forcing the sheet reference to be only "!" without actually having the sheet name in front)


VBA Code:
Sub RelativeRangeName_Loop()

    Dim Ws As Worksheet
    Dim rngAddress As Range
    Dim strName As String
    Dim i As Long, iSuffix As Long
  
    Set Ws = ActiveSheet
  
    For i = 56 To 80
        Set rngAddress = Range("D" & i)
        iSuffix = iSuffix + 1
        strName = "PCBMO" & Format(iSuffix, "_00")                ' <--- Change to suit
      
        ThisWorkbook.Names.Add Name:=strName, RefersToR1C1:="=" & rngAddress.Address(1, 0, xlR1C1, False, rngAddress)
    Next i

End Sub
 
Upvote 0
Solution
There are obviously going to be different ways of doing this, especially when it comes to giving the Named Range a name.
I have removed the Select it is not required after all, with the reference parameter in the address property handling that.
Since the column is relative you will need to be in Column D (active cell in Column D) if you want to see it show D in the RefersTo field in the Name manager.
Name manager will show whatever column you are in at the time.

Note: The names created will have the ActiveSheet hard coded in the name, so it matters which sheet is active at the time.
(There is a way of making it work on any sheet by forcing the sheet reference to be only "!" without actually having the sheet name in front)


VBA Code:
Sub RelativeRangeName_Loop()

    Dim Ws As Worksheet
    Dim rngAddress As Range
    Dim strName As String
    Dim i As Long, iSuffix As Long
 
    Set Ws = ActiveSheet
 
    For i = 56 To 80
        Set rngAddress = Range("D" & i)
        iSuffix = iSuffix + 1
        strName = "PCBMO" & Format(iSuffix, "_00")                ' <--- Change to suit
     
        ThisWorkbook.Names.Add Name:=strName, RefersToR1C1:="=" & rngAddress.Address(1, 0, xlR1C1, False, rngAddress)
    Next i

End Sub
Hey Alex - it works perfectly! I truly can't thank you enough; you have saved me hours of maintenance time!
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,568
Members
452,926
Latest member
rows and columns

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top