vba - 编译错误无效下一个控制变量引用VBA

Question

我不断收到编译错误无效下一个控制变量引用任何人都可以提供帮助。

我需要代码循环通过 Sheet1 列 A 并将值复制并粘贴到 Sheet2(R1) 然后循环通过 Sheet1 列 B 并将每个值复制粘贴到 Sheet2(I7) 然后将工作表保存为新的 PDF 文档

 Private Sub CommandButton1_Click()
Dim i As Long
Dim n As Long
Dim m As Integer
NumRows1 = Range("A2", Range("A2").End(xlDown)).Rows.Count
NumRows2 = Range("B2", Range("B2").End(xlDown)).Rows.Count

For i = 2 To NumRows1
    Range("i").Select
    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("R1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor

For n = 2 To NumRows2
Range("n").Select
    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("I7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
For m = 0 To 300
Sheets("Sheet2").Select
ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\" & CStr(m) & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=True, _
        OpenAfterPublish:=False
Next i
 Next n
   Next m
 Application.ScreenUpdating = True
End Sub

Answer 1

尝试这个

Sub Demo()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim lastRow As Long
    Dim cel As Range, rng As Range

    Set srcSht = ThisWorkbook.Sheets("Sheet1")  'this is your source sheet
    Set destSht = ThisWorkbook.Sheets("Sheet2") 'this is your destination sheet

    With srcSht
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    'get last row with data in Column A of srcSht
        For Each cel In .Range("A2:A" & lastRow)            'loop through each cell in Column A of srcSht
            cel.Copy destSht.Range("R1")                    'copy cell in Column A of srcSht to Cell R1 of destSht
            cel.Offset(0, 1).Copy destSht.Range("I7")       'copy cell in Column B of srcSht to Cell I7 of destSht

            Set rng = Union(destSht.Range("R1"), destSht.Range("I7"))   'union cell R1 and I7

            With rng.Font               'format union range
                .Name = "Calibri"
                .Size = 20
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = 0
                .ThemeFont = xlThemeFontMinor
            End With

            destSht.Range("I7").Font.Size = 16

            'I've not tested save as pdf file part
            destSht.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=ThisWorkbook.Path & "\" & (cel.Row - 1) & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=True, _
                OpenAfterPublish:=False
        Next cel
    End With
End Sub

注:我没有测试将文件保存为 pdf 部分。