Using VBA to create range names

[ElvisSteel]

Hi,

Been a while since I was here but my memory is of a very knowledgeable forum so hopefully someone can help!

I have a workbook in which I need to create a set of range names that effectively repeat 100 times.
By way of explanation, say:
Cells A1:A4 need to be called "Item_001_Header"
Cells A17:B20 need to be called "Item_001_Codes"
Cells A55:B60 need to be called "Item_001_Data"

Then C1:C4 = "Item_002_Header", C17:C20 = "Item_002_Codes"
etc etc

Rather than sit and type in 300 ranges by hand I am trying to do this using VBA.
The idea is that I loop around the 100 sets, using Add Name to create the ranges but I am having all sorts of problems with the R1C1 format.

My code is as follows (so far):

 For p = 1 To 100    rn = "Item_" & WorksheetFunction.Text(p, "000") & "_Header"
    ActiveWorkbook.Names.Add Name:=rn, RefersToR1C1:= _
        "='Sheet One'!R1C1:R4C1"
    
    rn = "Item_" & WorksheetFunction.Text(p, "000") & "_Codes"
    ActiveWorkbook.Names.Add Name:=rn, RefersToR1C1:= _
        "=#Sheet One'!R17C21:R20C2"
 Next p

However this is plainly wrong as I need to the name references to be relative but I cannot get it to work using the usual R[x]C[y] format.

Anyone able to point out my mistake(s)

Thanks

 

[andrewkard]

Hello, maybe so:

Sub test()
    Dim iCl%
    iCl = 1
    For p = 1 To 100
        rn = "Item_" & WorksheetFunction.Text(p, "000") & "_Header"
        ActiveWorkbook.Names.Add Name:=rn, RefersToR1C1:= _
                                 "='Sheet One'!R1C" & iCl & ":R4C" & iCl
        rn = "Item_" & WorksheetFunction.Text(p, "000") & "_Codes"
        ActiveWorkbook.Names.Add Name:=rn, RefersToR1C1:= _
                                 "='Sheet One'!R17C" & iCl & ":R20C" & iCl
        iCl = iCl + 2
    Next p
End Sub

 

[ElvisSteel]

Thanks Andrew for the prompt reply

 

[andrewkard]

Is it worked? I hadn't tested it.