ExcelVBA循环将数据从电子表格导出到Excel中的表

Question

我想嵌套一个for循环来循环Range.Value，但是("B4")抛出了我，这是无法避免的。我正在考虑使用数组来完成这个任务，但似乎无法得到正确的格式。"Q3“是一个电子表格，"Bac”是从一个必须保存的PDF中导入的附加表。这段代码工作得很好--但它很笨重！我有另外三个独特的PDF来完成类似的任务，而不仅仅是强迫这个东西工作，我想利用这个机会来提高我的VBA循环技能。

Private Sub CommandButton1_Click()

Dim lr As Long
Dim FolderPath As String

FolderPath = "C:\Users\Joe.Dimaggio\Desktop\PDFs"

lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column

For x = 2 To lr
    Sheets("Bac Form").Range("A2").Value = _
    Sheets("Q3").Cells(1, 1).Value & Sheets("Q3").Cells(x, 1).Value
    Sheets("Bac Form").Range("A3").Value = _
    Sheets("Q3").Cells(1, 2).Value & Sheets("Q3").Cells(x, 2).Value & _
    " (Third Bacterial Quarter)"
    Sheets("Bac Form").Range("A4").Value = _
    Sheets("Q3").Cells(1, 3).Value & Sheets("Q3").Cells(x, 3).Value
    Sheets("Bac Form").Range("B4").Value = _
    Sheets("Q3").Cells(1, 4).Value & Sheets("Q3").Cells(x, 4).Value
    Sheets("Bac Form").Range("A5").Value = _
    Sheets("Q3").Cells(1, 5).Value & Sheets("Q3").Cells(x, 5).Value
    Sheets("Bac Form").Range("A6").Value = _
    Sheets("Q3").Cells(1, 6).Value & Sheets("Q3").Cells(x, 6).Value
    Sheets("Bac Form").Range("A7").Value = _
    Sheets("Q3").Cells(1, 7).Value & Sheets("Q3").Cells(x, 7).Value
    Sheets("Bac Form").Range("A8").Value = _
    Sheets("Q3").Cells(1, 8).Value & Sheets("Q3").Cells(x, 8).Value
    Sheets("Bac Form").Range("A9").Value = _
    Sheets("Q3").Cells(1, 9).Value & Sheets("Q3").Cells(x, 9).Value
    Sheets("Bac Form").Range("A10").Value = _
    Sheets("Q3").Cells(1, 10).Value & Sheets("Q3").Cells(x, 10).Value
    Sheets("Bac Form").Range("A11").Value = _
    Sheets("Q3").Cells(1, 11).Value & Sheets("Q3").Cells(x, 11).Value
    Sheets("Bac Form").Range("A13").Value = _
    Sheets("Q3").Cells(1, 12).Value & Sheets("Q3").Cells(x, 12).Value
    Sheets("Bac Form").Range("A14").Value = _
    Sheets("Q3").Cells(1, 13).Value & Sheets("Q3").Cells(x, 13).Value
    Sheets("Bac Form").Range("A15").Value = _
    Sheets("Q3").Cells(1, 14).Value & Sheets("Q3").Cells(x, 14).Value & _
    " colony/100 ml"
    Sheets("Bac Form").Range("A16").Value = _
    Sheets("Q3").Cells(1, 15).Value & Sheets("Q3").Cells(x, 15).Value
    Sheets("Bac Form").Range("A17").Value = _
    Sheets("Q3").Cells(1, 16).Value & Sheets("Q3").Cells(x, 16).Value & _
    " MPN/100 ml"
    Sheets("Bac Form").Range("A18").Value = _
    Sheets("Q3").Cells(1, 17).Value & Sheets("Q3").Cells(x, 17).Value



        Worksheets("Bac Form").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" & _
        Worksheets("Bac Form").Name & "(Q3)" & (x - 1), openafterpublish:=False

Next x



End Sub

以下是导入Excel后的PDF表单：

Answer 1

我在这里很难想象你的数据，截图会很有用。

同时：

Option Explicit

这应该是您在VBA中创建的每个代码模块的顶部。转到Tools -> Options -> Require声明，从现在开始它将自动为您插入它。

这一点很重要，因为没有它，VBA将把任何新的变量名(包括拼写错误)解释为全新的变量，而不是您想要的。

它也迫使你声明你的变量。因此，您必须显式地给它们一个类型(长、字符串、变体等)。和一个范围( Project var As，Module Private，Project Public Var As)。

然后，这将自动捕获各种意外情况(例如意外设置一个与对象相等的数字)，如果VBA必须假定所有变量都是可变的，因为您从未显式声明它们，则不会捕捉到这些情况。

幻数变量

也不是那种好的魔法。魔术变量是硬编码的任何值。尤其是如果它被硬编码在多个地方。

lr = Cells(Rows.Count, 1).End(xlUp).Row

为什么是1？这个数字是如何确定的？

"Bac Form", "A1", "Q3" etc.

为什么要特别强调这些价值观？如果您不能查看一个变量并知道它代表了什么，那么它就在您的代码中出现了，就像魔术一样。

如果您必须对值进行硬编码，那么它们应该被硬编码一次，然后被硬编码成一个描述性命名的变量。

这是：

FolderPath = "C:\Users\Joe.Dimaggio\Desktop\PDFs"

是硬编码值的正确方法。它只编写了一次，我可以在后面的代码中看到FolderPath，并且确切地知道它是什么。

这是：

.Range(“A2”).Value=_ Sheets("Q3").Cells(1，1).Value & Sheets("Q3").Cells(x，1).Value Sheets("Bac Form").Range("A3").Value =_ Sheets("Q3").Cells(1，2).Value & Sheets("Q3").Cells(x，( 2).Value &_(第三个细菌区)“Sheets("Bac Form").Range("A4").Value =_ Sheets("Q3").Cells(1，3).Value & Sheets("Q3").Cells(x，3).Value Sheets("Bac Form").Range("B4").Value =_ Sheets("Q3").Cells(1，4).Value &Sheets(”Q3“).Value(x，( 4).Value Sheets("Bac Form").Range("A5").Value =_ Sheets("Q3").Cells(1，5).Value & Sheets("Q3").Cells(x，5).Value Sheets("Bac Form“).Range(A6).Value=_ Sheets("Q3").Cells(1，6).Value & Sheets("Q3").Cells(x，( 6).Value Sheets("Bac Form").Range("A7").Value =_ Sheets("Q3").Cells(1，7).Value & Sheets("Q3").Cells(x，7).Value Sheets("Bac Form“).Range(A8).Value=_ Sheets("Q3").Cells(1，8).Value & Sheets("Q3").Cells(x，( 8).Value Sheets("Bac Form").Range("A9").Value =_ Sheets("Q3").Cells(1，9).Value & Sheets("Q3").Cells(x，9).Value Sheets("Bac Form“).Range(A10).Value=_ Sheets("Q3").Cells(1，10).Value & Sheets("Q3").Cells(x，10).Cells(x，10).Value =_Sheets(”Q3“).Cells(1，10).Value(”.Cells“).Cells(”Q3“).Cells(”.Value“).Cells(”.Value“).Cells(”Q3“).Cells(1，10).Value&Sheets(”Q3“)Q3(x，10).Value(”.Value“).Value=_Sheets(”Q3“).Cells(1，10).Value&Sheets(”Q3“

不是。如果工作表的名称更改，会发生什么情况？还是您的数据表因为有人插入/删除了列/行而移动？你得去重新编码每一个值。

命名

变量名称应该简洁、描述性好，最重要的是，要明确。

FolderPath

是一个很好的名称，因为它明确了变量包含/表示的内容。

lr

不是。如果我在您的代码中遇到了这一行代码，我会一直走回去，提醒自己它代表的是什么。只要叫它lastRow，你就可以避免所有的麻烦。

VBA命名约定：

典型的VBA命名约定如下：

局部变量的camelCase。

模块/全局变量的PascalCase。

常数的SHOUTY_SNAKE_CASE。

养成跟随他们的习惯。

把一切都聚集在一起，

步骤1，将工作表放入变量中：

For x = 2 To lr
    wsBacForm.Range("A2").Value = wsQ3.Cells(1, 1).Value & wsQ3.Cells(x, 1).Value
    wsBacForm.Range("A3").Value = wsQ3.Cells(1, 2).Value & wsQ3.Cells(x, 2).Value & " (Third Bacterial Quarter)"
    wsBacForm.Range("A4").Value = wsQ3.Cells(1, 3).Value & wsQ3.Cells(x, 3).Value
    wsBacForm.Range("B4").Value = wsQ3.Cells(1, 4).Value & wsQ3.Cells(x, 4).Value
    wsBacForm.Range("A5").Value = wsQ3.Cells(1, 5).Value & wsQ3.Cells(x, 5).Value
    wsBacForm.Range("A6").Value = wsQ3.Cells(1, 6).Value & wsQ3.Cells(x, 6).Value
    wsBacForm.Range("A7").Value = wsQ3.Cells(1, 7).Value & wsQ3.Cells(x, 7).Value
    wsBacForm.Range("A8").Value = wsQ3.Cells(1, 8).Value & wsQ3.Cells(x, 8).Value
    wsBacForm.Range("A9").Value = wsQ3.Cells(1, 9).Value & wsQ3.Cells(x, 9).Value
    wsBacForm.Range("A10").Value = wsQ3.Cells(1, 10).Value & wsQ3.Cells(x, 10).Value

突然，结构变得清晰起来。

：

添加选项以更改表的行/列位置。

循环遍历行/列。

为每个工作表添加单独的变量，以处理(可能)不同的位置。

添加索引计数器以跟踪插入额外文本的位置。

将文件名创建放在单独的行上。

添加适当的范围对象。

现在，如果您需要更改某些内容，或者您的数据表变得更大，或者您需要修改执行的一个特定方面，那么您只需更改一个值，其余的就可以为您完成。

Private Sub CommandButton1_Click()

    Dim fileName As String

    Const FOLDER_PATH As String = "C:\Users\Joe.Dimaggio\Desktop\PDFs"

    Dim wsBacForm As Worksheet, wsQ3 As Worksheet
    Set wsBacForm = Sheets("Bac Form")
    Set wsQ3 = Sheets("Q3")

    Dim q3BaseRow As Long, q3BaseCol As Long '/ Location of the Q3 data table
    q3BaseRow = 1
    q3BaseCol = 1

    Dim bacBaseRow As Long, bacBaseCol As Long '/ Location of the bac output table
    bacBaseRow = 1
    bacBaseCol = 1

    Dim lastRow As Long, lastCol As Long
    lastRow = wsQ3.Cells(Rows.Count, q3BaseCol).End(xlUp).row
    lastCol = wsQ3.Cells(q3BaseRow, Columns.Count).End(xlToLeft).Column

    Dim bacRow As Long, bacCol As Long, q3Row As Long, q3Col As Long
    Dim row As Long, col As Long, counter As Long

    Dim bacOutputCell As Range, q3HeaderCell As Range, q3DataCell As Range
    Dim outputString As String

    For row = q3BaseRow To lastRow
        bacRow = row - q3BaseRow + bacBaseRow
        q3Row = row

        counter = 0
        For col = q3BaseCol To lastCol
            counter = counter + 1
            q3Col = col

            Set bacOutputCell = wsBacForm.Cells(bacRow, bacBaseCol)
            Set q3HeaderCell = wsQ3.Cells(q3BaseRow, q3Col)
            Set q3DataCell = wsQ3.Cells(q3Row, q3Col)

            outputString = q3HeaderCell.Value & q3DataCell.Value

            Select Case counter '/ used a counter so that it is (absolute position of column) agnostic

                Case Is = 2
                outputString = outputString & " (Third Bacterial Quarter)"

                Case Is = 14
                outputString = outputString & " colony/100 ml"

                Case Is = 16
                outputString = outputString & " MPN/100 ml"

            End Select

            bacOutputCell.Value = outputString
        Next col

        fileName = FOLDER_PATH & "\" & wsBacForm.Name & "(" & wsQ3.Name & ")" & (row - 1)
        wsBacForm.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, openafterpublish:=False
    Next row

End Sub