Relative Column Absolute Row Code

[SewStage]

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...?

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

 

[rlv01]

Range.Address property (Excel)

Office VBA reference topic

learn.microsoft.com

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.

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

 

[SewStage]

Range.Address property (Excel)

Office VBA reference topic

learn.microsoft.com

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.

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.

 

[Alex Blakenburg]

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

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

 

[SewStage]

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

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?

 

[SewStage]

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

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??

 

[Alex Blakenburg]

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.

 

[SewStage]

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!

 

[Alex Blakenburg]

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)

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

 

[SewStage]

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)

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!

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.