excel - 如果数据包含某些数据，则将数据复制到新工作表

Question

我想从报告中提取某些数据并将其吐出到新工作表中。

我已经能够从文本文件中提取数据并粘贴到模板中的新工作表中，找到我需要开始查找相关数据的位置，并将第一行粘贴到目标工作表中。

我想知道设置代码的更复杂的方法。我认为循环可能是最好的，但数组也可以解决问题。

这是我到目前为止所拥有的：

Sub findandmovetxt()

'finding text data and moving to new sheet

    Dim celltxt As String

    Sheets("BAI").Activate
    Cells.find(what:="811", After:=ActiveCell).Activate
    Cells.find(what:="16,175", After:=ActiveCell).Activate
    Selection.Copy
    Sheets("Bank").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Sheets("BAI").Activate
    ActiveCell.Offset(1).Select
    celltxt = ActiveCell.Text
    If InStr(1, celltxt, "16,175") = True Then Selection.Copy
    If InStr(1, celltxt, "16,175") = False Then End

End Sub

相关数据是大型银行报告的一部分。我想复制“811”帐户中包含“16,175”的行并将它们粘贴到“银行”表中。

在“16,175”行之后，总是有一个“16,275”我不想拾取，并表示相关帐户中“16,175”数据的结束。

我最终将对报告中更靠后的其他银行账户使用相同的代码。

我需要知道如何使用循环（或数组），但不确定如何在这种情况下实施，因为我想关注特定帐户中的数据，而不是整个报告。

标签： excelvbaimport-from-csv

Answer 1

Public Sub findandmovetxt()

Dim wbBankReport As Workbook: Set wbBankReport = ThisWorkbook 'This assumes the data you want to look at is in the same workbook that contains your code.

'The next two lines require that your workbook already contains a worksheet named "BAI" and a worksheet named "Bank".
'Typically you would write error handling code to ensure these tabs exist and to create them if they don't.
Dim wsBAI As Worksheet: Set wsBAI = wbBankReport.Worksheets("BAI") 'This tells the code where your BAI data is.
Dim wsBANK As Worksheet: Set wsBANK = wbBankReport.Worksheets("Bank") 'This tells the code where you want to put your data.
Dim i0 As Long 'This is a counter you will use to cycle through the lines.
Dim i1 As Long: i1 = 1 'This is a counter you will use to add lines to the Bank tab.
Dim strLine As String 'This is a variable that will contain the data from each line.
Dim blnCOPY As Boolean 'This is a variable that will tell the code when to copy and when to stop.

With wsBAI
    For i0 = 1 To .UsedRange.Rows.Count 'This says you want to start at row 1 and go to the end of the populated lines in your BAI worksheet.
        strLine = .Cells(i0, 1).Value 'This copies the value of each cell into the variable.
        If InStr(strLine, ",811,") > 0 Then blnCOPY = True
            'Instead of just looking for 811, the InStr function (In String) is looking for 811 preceded and followed by a comma just in case the series 811 appears anywhere else that might throw your search off.
            'Setting the variable blnCOPY to TRUE tells the code you want to copy the appropriate following lines.
        If InStr(strLine, "16,175,") > 0 And blnCOPY = True Then
            wsBANK.Cells(i1, 1).Value = strLine 'This moves the data to the Bank worksheet.
            i1 = i1 + 1 'This increments the row number.
        End If
        If InStr(strLine, "16,275,") > 0 Then blnCOPY = False 'This tells the code you don't want to copy any lines until it sees the ",811," again.
    Next i0
End With
End Sub