creating set of named ranges

[Steve]

I have a workbook containing many named ranges. 174 of these form a set, in
that they are defined by standard OFFSET functions in which the parameters
are calculated in a structured block of cells in a worksheet called
"Ranges". So, for example, the range named "RecDatPre" is defined by the
formula
=OFFSET(Rec!$A$1,Ranges!$B$16,Ranges!$B$4,Ranges!$C$16,1)

I want to create a second set of 174 named ranges similar to the first set,
using a different (but identically structured) block of cells in "Ranges".
Each name will be the name from the first set with the letters "Cfb"
appended. Each formula will be the same as that from the first set except
that the row numbers in the references of the second and fourth parameters
(number of rows and height) will be 15 greater than those in the first set.
So, the named range in the second set derived from the example above would
be named "RecDatPreCfb" and defined by the formula
=OFFSET(Rec!$A$1,Ranges!$B$31,Ranges!$B$4,Ranges!$C$31,1)

Creating these manually would be time-consuming and prone to error. I assume
that this could be done with a macro, but my abilities with VBA are not
great. I would appreciate any code (or other suggestions) that anyone can
offer.

 

[J.E. McGimpsey]

It's not pretty, but one way:

Public Sub AddNames()
Const SETSTR As String = "Rec*"
Const ENDSTR As String = "Cfb"
Const SHTNAME As String = "Ranges!"
Dim nmName As Name
Dim iPos As Integer
Dim cLen As Integer
Dim fStr As String

For Each nmName In ThisWorkbook.Names
With nmName
If (.Name Like SETSTR) And _
(Right(.Name, 3) <> ENDSTR) Then
fStr = .RefersTo
iPos = InStr(fStr, SHTNAME)
If iPos Then
iPos = iPos + 7
cLen = InStr(iPos, fStr, ",") - iPos
Mid(fStr, iPos, cLen) = _
Range(Mid(fStr, iPos, cLen)).Offset( _
15, 0).Address(True, True)
iPos = InStr(iPos + cLen, fStr, SHTNAME) + 7
iPos = InStr(iPos + 1, fStr, SHTNAME) + 7
cLen = InStr(iPos, fStr, ",") - iPos
Mid(fStr, iPos, cLen) = _
Range(Mid(fStr, iPos, cLen)).Offset( _
15, 0).Address(True, True)
ThisWorkbook.Names.Add .Name & ENDSTR, fStr
End If
End If
End With
Next nmName
End Sub


I assumed that all of the names in the "set" started with "Rec" -
adjust SETSTR as necessary

 

[Steve]

This was a really useful reply. The code worked without any trouble. It
saved me hours of tedious work. Thanks a million!