Using Macros to name ranges in Excel 2016

[IRISHSHWDOW]

I want use macros to create different names for different ranges I have made (about 81 in total). The names vary slightly, for example one is called "Numbers_Left_G3" and another is "Numbers_Left_H5". The names depend on the cell above the range for example the former would have a cell above it that says "Left G3" and the latter would have a cell above it that reads "Left H5". I am extremely new (I'm a newb) to using macros so don't know the best way of going about this. I was attempting to use some variation of the RIGHT and ADDRESS formulas such as:
RIGHT(ADDRESS(ROW(ActiveCell.Select)-1,COLUMN(ActiveCell.Select),4,1),2)
but that wasn't working.
Any help is much appreciated!

 

[Yongle]

If named ranges Numbers_Left_G3 and Numbers_Left_H5 were created manually, what would be the RefersTo FORMULA for each?

Answer not required to this (has no impact in answering your question) but it is something for you to consider:
- will your names stand the test of time?
- new rows and columns are often inserted as worksheets develop
- would inserting a new row above or a column to the left of a named range at a future date render the names meaningless
- example insering row 2 would move G3 to G4 and H5 to H6

 

[IRISHSHWDOW]

Numbers_Left_G3 refers to: =OFFSET(Sheet2!$AC$26,0,0,Sheet2!$AD$26,1)
Numbers_Left_H5 refers to: =OFFSET(Sheet2!$AH$50,0,0,Sheet2!$AH$50,1)

 

[IRISHSHWDOW]

Numbers_Left_H5 refers to: =OFFSET(Sheet2!$AH$50,0,0,Sheet2!$AI$50,1)

 

[Yongle]

I am not sure how you are wanting to create the names
- individually, via a loop, ask for user input at the time etc

RefersTo string can be automated based on the cell containing "Left H5" etc
- Numbers_Left_G3 refers to: =OFFSET(Sheet2!$AC$26,0,0,Sheet2!$AD$26,1)
- Numbers_Left_H5 refers to: =OFFSET(Sheet2!$AH$50,0,0,Sheet2!$AI$50,1)

This may help you get started and perhaps that will lead to further questions
- it creates the 2 names you specified

To test
- delete both names and run this macro to see if the 2 names are created correctly
(the macro does delete each name before adding it afresh but, unless deleted beforehand, you will not know if the macro created them or not )
- Debug.Print writes various values to the immediate window (helpful to see what is going wrong when testing!)
- delete that line after testing
- make Immediate Window visible in VBA window with shortcut {CTRL} G or menu item View \ Immediate Window

Sub AddNamedRange()
    
    Dim Nm As String, Addr As Variant, Cel As Range
    Dim Wb As Workbook: Set Wb = ThisWorkbook
    Dim Ws As Worksheet: Set Ws = Wb.Sheets("Sheet2")
   
    For Each Addr In Split("AC25,AH49", ",")
        Set Cel = Ws.Range(Addr)
        Nm = "Numbers_" & Replace(Cel.Value, " ", "_")
        On Error Resume Next
        Wb.Names(Nm).Delete
        Wb.Names.Add Name:=Nm, RefersTo:="=OFFSET(Sheet2!" & Cel.Offset(1).Address & ",0,0,Sheet2!" & Cel.Offset(1, 1).Address & ",1)"
        [I][COLOR=#0000cd]Debug.Print[/COLOR][/I] Cel.Address, Cel.Value, Nm, Wb.Names(Nm).Name, Wb.Names(Nm).RefersTo
    Next
   
End Sub

 

[IRISHSHWDOW]

This is almost perfect, thank you so much. The only thing I'd like to be different is instead of inputting cell in this line:
For Each Addr In Split("AC25,AH49", ",")
I'd like it to just use the cell I've currently selected. For example if I'm on cell AC25 and execute the macro it reads:
For Each Addr In Split("AC25", ",")
or if I'm on cell AH49 it reads:
For Each Addr In Split("AH49", ",")
...I feel I'm explaining this bad. But if this is possible I'd like to know how. If not then it is still very useful as is and will save me tons of time, thank you!

 

[Yongle]

Try this for currently selected cell only

Sub AddNamedRange()
    Dim Nm As String, Addr As Variant, Cel As Range
    Dim Wb As Workbook: Set Wb = ThisWorkbook
    Dim Ws As Worksheet: Set Ws = Wb.Sheets("Sheet2")

    Set Cel = ActiveCell
    Nm = "Numbers_" & Replace(Cel.Value, " ", "_")
    On Error Resume Next
    Wb.Names(Nm).Delete
    Wb.Names.Add Name:=Nm, RefersTo:="=OFFSET(Sheet2!" & Cel.Offset(1).Address & ",0,0,Sheet2!" & Cel.Offset(1, 1).Address & ",1)"
    Debug.Print Cel.Address, Cel.Value, Nm, Wb.Names(Nm).Name, Wb.Names(Nm).RefersTo 
End Sub

 

[IRISHSHWDOW]

This works perfectly thanks!

 

[Yongle]

Thanks for your feedback