vba 如何在excel vba中将变量设置为列范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28256857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set a variable as column range inside excel vba
提问by Gallaxhar
I have a named range for an entire table's column named DAY I have a macro that sets pagebreaks every time a cells value in the DAY column changes (like changes from day 1, to day 2, or day 3, there will be a page break for printing). However, this macro currently works by specifying the letter column like "A" or "B" or "C" or "H", how can I get it to work by specifying the "DAY" named range so if it moves, the vba code doesn't break?
我有一个名为 DAY 的整个表格列的命名范围我有一个宏，每次 DAY 列中的单元格值更改时都会设置分页符（例如从第 1 天到第 2 天或第 3 天的更改，会有一个分页符用于打印）。但是，此宏当前通过指定字母列（如“A”或“B”或“C”或“H”）来工作，如何通过指定“DAY”命名范围使其工作，因此如果它移动，vba代码不破解？
Special attention to this:
特别注意这一点：
For Each c In Range("C1:C" & lastrow)
Specifically:
具体来说：
Range("C1:C"
I want to change to:
我想改成：
Range("DAY"
but this breaks in various syntax forms I tried
但这打破了我尝试过的各种语法形式
Sub Set_PageBreaks_DAY()
Dim lastrow As Long, c As Range
Dim i As Integer, rngData As Range
Set rngData = Range("A1").CurrentRegion
i = Application.WorksheetFunction.Match("DAY", Range("A1:AZ1"), 0)
lastrow = Cells(Rows.Count, i).End(xlUp).Row
Application.ScreenUpdating = False
ActiveSheet.ResetAllPageBreaks
For Each c In Range("C1:C" & lastrow)
If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then
c.Offset(1, 0).PageBreak = xlPageBreakManual
End If
Next c
Application.ScreenUpdating = True
End Sub
回答by Degustaf
First, it is important to note that Named ranges have 2 possible scopes which will affect how to access it. If your named range has workbook scope, then you should use
首先，重要的是要注意命名范围有 2 个可能的范围，这将影响如何访问它。如果您的命名范围具有工作簿范围，那么您应该使用
Dim Named_range_day as Range
Set Named_range_day = ThisWorkbook.Names("Day").RefersToRange
If the named range has worksheet scope, then use
如果命名范围具有工作表范围，则使用
Dim Named_range_day as Range
Set Named_range_day = wksht.Names("Day").RefersToRange
where wkshtis the worksheet variable for the worksheet containing the named range.
其中wksht是包含命名范围的工作表的工作表变量。
回答by Gallaxhar
The reason JLILI Aman's answer didn't work is you have to convert the column index number to a column letter first using
JLILI Aman 的答案不起作用的原因是您必须首先使用将列索引号转换为列字母
columnLetter = Split(Columns(i).Address(), "$")(2)
So for example
所以例如
Sub Set_PageBreaks_CREW()
Dim lastrow As Long, c As Range
Dim i As Integer, rngData As Range
Set rngData = Range("A1").CurrentRegion
i = Application.WorksheetFunction.Match("DAY", Range("A1:AZ1"), 0)
lastrow = Cells(Rows.Count, i).End(xlUp).Row
Application.ScreenUpdating = False
ActiveSheet.ResetAllPageBreaks
columnLetter = Split(Columns(i).Address(), "$")(2)
Var = columnLetter & "1:" & columnLetter
For Each c In Range(Var & lastrow)
If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then
c.Offset(1, 0).PageBreak = xlPageBreakManual
End If
Next c
Application.ScreenUpdating = True
End Sub
