excel 如何使用For/Next循环根据复选框状态打印范围

amrnrhlw  于 2023-06-25  发布在  其他
关注(0)|答案(3)|浏览(157)

我在编写一些代码时遇到了麻烦,这些代码允许我查看复选框的状态,并在复选框等于true时在另一个工作表上打印指定的范围,然后对给定数量的复选框和范围重复该循环。
我使用两个不同的For - Next循环来尝试完成这个任务。下面是我目前的代码。

Sub PrintGraphs()
Dim i As Integer
Dim r As Integer
    For i = 16 To 18
        If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = True Then
            For r = 1 To 3
                Worksheets("Cut Tables Graphs").Activate
                    Range("RangeSet" & r).Select
                    ActiveSheet.PageSetup.PrintArea = ("RangeSet" & r)
                    With ActiveSheet.PageSetup
                        .PaperSize = xlPaperLetter
                        .Orientation = xlLandscape
                        .Zoom = False
                        .FitToPagesWide = 1
                        .FitToPagesTall = 1
                        .LeftMargin = Application.InchesToPoints(0.25)
                        .RightMargin = Application.InchesToPoints(0.25)
                        .TopMargin = Application.InchesToPoints(0.25)
                        .BottomMargin = Application.InchesToPoints(0.25)
                        .CenterHorizontally = True
                        .CenterVertically = True
                    End With
                    Application.PrintCommunication = True
                    Selection.PrintOut Copies:=1, Collate:=True
            Next r
        End If
    Next i
End Sub

我还在一个单独的模块上使用公共常量来定义变量“RangeSet”。该模块上的代码如下所示。

Public Const RangeSet1 = "B2:U51"
Public Const RangeSet2 = "W2:AP51"
Public Const RangeSet3 = "B53:U102"

我希望我的代码查看复选框16到18,然后打印复选框equals true的值中的相应范围。
预期结果:
如果复选框#16等于真,则打印范围“B2:U 51”,否则下一个为
如果复选框#17等于真,则打印范围“W2:AP 51”,否则下一个为
如果复选框#18等于真,则打印范围“B53:U102”,否则结束循环
我现在遇到的错误是:运行时错误“1004”:包含“Range(“RangeSet”& r)的行上的调用目标引发了异常。请选择
任何帮助找到一个解决这个问题的方法将不胜感激。先谢谢你。

xmjla07d

xmjla07d1#

我觉得最简单的方法是用你想记录的值创建一个数组。一旦你填充了数组,你就可以使用for循环将它打印到任何你想要的地方。

v2g6jxz6

v2g6jxz62#

Range("RangeSet" & r).Select

在VBA中不是有效范围。你将以字符串的形式传递“RangeSet1”,而不是一个范围,也不是你声明的变量。
可以试试

if r = 1 then
     Range(RangeSet1).Select
elseif r=2 then
     Range(RangeSet2).Select
elseif r=3 then
     Range(RangeSet3).Select
else
    stop'should never get here?
endif

有一种方法可以在一行代码中完成这一点,但我不记得它的语法。

8ehkhllq

8ehkhllq3#

这就是我写代码的方式

Sub PrintGraphs()

    Dim i As Integer

    With ThisWorkbook.Worksheets("Cut Tables Graphs")
        With .PageSetup
            .PaperSize = xlPaperLetter
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.25)
            .BottomMargin = Application.InchesToPoints(0.25)
            .CenterHorizontally = True
            .CenterVertically = True
        End With
        Application.PrintCommunication = True
        For i = 16 To 18
            If .OLEObjects("CheckBox" & i).Object.Value = True Then

                .Range(Choose(i - 15, RangeSet1, RangeSet2, RangeSet3)).PrintOut Copies:=1, Collate:=True

            End If
        Next i
    End With

End Sub

不需要变量r -如果你选择CheckBox 16你想打印范围1 -所以从i中删除15,你得到1 -选择那个范围(希望有意义)。
我还假设“ActiveSheet”(带有勾选框的那个)是“Cut Tables Graphs”。
我在底部包含了您的原始代码和一些注解。

Sub PrintGraphs()
Dim i As Integer
Dim r As Integer
    'This will work - providing the correct sheet is active.
    For i = 16 To 18
        If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = True Then

            'This will cycle through all three no matter which checkbox was ticked.
            For r = 1 To 3

                'No need to activate the sheet.  Especially if it's the ActiveSheet.
                Worksheets("Cut Tables Graphs").Activate

                    'No need to select a range before working on it.
                    Range("RangeSet" & r).Select

                    'This only needs doing once - not at all if it's the default anyway.
                    'As it's inside the 1 to 3 loop it will happen 3 times.
                    ActiveSheet.PageSetup.PrintArea = ("RangeSet" & r)
                    With ActiveSheet.PageSetup
                        .PaperSize = xlPaperLetter
                        .Orientation = xlLandscape
                        .Zoom = False
                        .FitToPagesWide = 1
                        .FitToPagesTall = 1
                        .LeftMargin = Application.InchesToPoints(0.25)
                        .RightMargin = Application.InchesToPoints(0.25)
                        .TopMargin = Application.InchesToPoints(0.25)
                        .BottomMargin = Application.InchesToPoints(0.25)
                        .CenterHorizontally = True
                        .CenterVertically = True
                    End With
                    Application.PrintCommunication = True

                    'Rather than select the range and print the selection,
                    'just reference the range and print it.
                    Selection.PrintOut Copies:=1, Collate:=True
            Next r
        End If
    Next i
End Sub

相关问题