断开代码中分配给命名范围的地址与生成的命名范围地址之间的连接
我正在尝试在 Excel 2003（不是我选择的版本）中编写 VBA 脚本，将工作表上的预定义范围划分为十个命名范围。工作表名称是“paste_data”，我想要将脚本限制到的单元格“块”是 A4:AO111。有时，当我运行脚本时，它可以工作，但在其他时候，它似乎将有效的起始单元格从 A4 转移到另一个单元格。这是一个不良结果的示例（抱歉，我无法发布图像，因为我是新人）：
命名范围 table.emergency.count 引用了范围 V6:AO25，而它应该引用范围 V4:AO23。
我的代码在这里：
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
有谁知道为什么会发生这种情况？我的直觉是工作表的“usedrange”是罪魁祸首。
I am trying to 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当您在定义的名称中使用相对引用时，定义是相对于活动单元格的。为了避免这种情况，请使用绝对引用，例如 $V$4:$AO$23。使用绝对引用，命名范围将始终指向相同的单元格。
例子：
选择单元格 A1 并将名称 test_relative 定义为“=A1”。现在选择单元格 B10 并重新打开定义的名称框，选择 test_relative，您将看到类似“=Sheet1!B10”的内容。
要修复代码，请在范围引用中插入 $
另请注意，
尺寸 rw1 作为变体。使用
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
Also note that
dimensions rw1 as a Variant. Use