Disconnect between address assigned in code to named range and the resulting named range address

I am trying t开发者_运维问答o write a VBA script in Excel 2003 (not my choice of version) to partition a predefined range on a worksheet into ten named ranges. The worksheet name is "paste_data" and the 'block' of cells that I want to confine the script to is A4:AO111. Sometimes, when I run the script, it works, but at other times, it seems to shift the effective starting cell from A4 to another cell. Here is an example of bad results (sorry, I can't post an image because I'm new):

The named range table.emergency.count refers to range V6:AO25 when it should refer to range V4:AO23.

My code is here:

Sub tables_assign()
Dim j As Integer
Dim range_ref, range_name, rref As String
Dim tbles(1 To 10) As String
Dim rw1, rw2 As Integer
'##########################################################################################
'CREATION AND NAMING OF TABLES
'##########################################################################################
tbles(1) = "table.emergency.score": tbles(2) = "table.emergency.count": tbles(3) = "table.eol.score": tbles(4) = "table.eol.count": tbles(5) = "table.inpatient.score": tbles(6) = "table.inpatient.count": tbles(7) = "table.outpatient.score": tbles(8) = "table.outpatient.count": tbles(9) = "table.sds.score": tbles(10) = "table.sds.count"
For j = 1 To 10
    If j Mod 2 <> 0 Then
        If j = 1 Then
            rw1 = 4
            rw2 = 23
        Else
            rw1 = 4 + 22 * Application.WorksheetFunction.Ceiling((j / 2 - 1), 1)
            rw2 = 23 + 22 * Application.WorksheetFunction.Ceiling((j / 2 - 1), 1)
        End If
        rref = Trim(Application.WorksheetFunction.Substitute("=paste_data!A" & Str(rw1) & ":T" & Str(rw2), " ", ""))
        ActiveWorkbook.Names.Add tbles(j), rref
    Else
        If j = 2 Then
            rw1 = 4
            rw2 = 23
        Else
            rw1 = 4 + 22 * (j / 2 - 1)
            rw2 = 23 + 22 * (j / 2 - 1)
        End If
        rref = Trim(Application.WorksheetFunction.Substitute("=paste_data!V" & Str(rw1) & ":AO" & Str(rw2), " ", ""))
        ActiveWorkbook.Names.Add tbles(j), rref
    End If
Next j
End Sub

Does anyone have an idea why this would happen? My hunch is that the worksheet's 'usedrange' is the culprit.

When you use relative references in defined names, the definition is relative to the activecell. To avoid that, use absolute references, like $V$4:$AO$23. With absolute references, the named range will always point to the same cells.

Example: Select cell A1 and define the name test_relative as "=A1". Now select cell B10 and reopen the defined name box, select test_relative and you'll see something like "=Sheet1!B10"

To fix your code, insert the $ in the range references

rref = Trim(Replace("=paste_data!$A$" & Str(rw1) & ":$T$" & Str(rw2), " ", ""))

Also note that

Dim rw1, rw2 As Integer

dimensions rw1 as a Variant. Use

Dim rw1 As Integer, rw2 As Integer