VBA实现：在“w附注”表精准查找特定资产，按余额条件复制粘贴到“附注校验”表

以下是一个使用 VBA 来解决此问题的示例代码： ```vb Sub FindAndCopyData() Dim wsWNote As Worksheet Dim wsCheck As Worksheet Dim findRange As Range Dim lastRow As Long Dim copyRange As Range Dim i As Long Dim startRow As Long Dim endRow As Long Dim found As Boolean Set wsWNote = ThisWorkbook.Sheets("w 附注") Set wsCheck = ThisWorkbook.Sheets("附注校验") lastRow = wsWNote.Cells(wsWNote.Rows.Count, 1).End(xlUp).Row For i = 1 To lastRow Set findRange = wsWNote.Cells(i, 1).EntireRow If InStr(1, findRange.Value, "分类为以公允价值计量且其变动计入当期损益的金融资产", vbTextCompare) > 0 Then found = True If InStr(1, wsWNote.Cells(i - 1, 1).Value, "期末余额", vbTextCompare) > 0 Then startRow = i + 2 endRow = FindFirstSumRow(startRow, wsWNote) - 1 Set copyRange = wsWNote.Range(wsWNote.Cells(startRow, 1), wsWNote.Cells(endRow, wsWNote.Columns.Count)) copyRange.Copy wsCheck.Rows(221).PasteSpecial xlPasteValues Set copyRange = wsWNote.Cells(FindFirstSumRow(startRow, wsWNote), 1) copyRange.Copy wsCheck.Rows(227).PasteSpecial xlPasteValues ElseIf InStr(1, wsWNote.Cells(i - 1, 1).Value, "期初余额", vbTextCompare) > 0 Then startRow = i + 2 endRow = FindFirstSumRow(startRow, wsWNote) - 1 Set copyRange = wsWNote.Range(wsWNote.Cells(startRow, 1), wsWNote.Cells(endRow, wsWNote.Columns.Count)) copyRange.Copy wsCheck.Rows(234).PasteSpecial xlPasteValues Set copyRange = wsWNote.Cells(FindFirstSumRow(startRow, wsWNote), 1) copyRange.Copy wsCheck.Rows(240).PasteSpecial xlPasteValues End If End If Next i If Not found Then MsgBox "未找到指定内容" End If End Sub Function FindFirstSumRow(startRow As Long, ws As Worksheet) As Long Dim i As Long For i = startRow To ws.Rows.Count If InStr(1, ws.Cells(i, 1).Value, "合计", vbTextCompare) > 0 Then FindFirstSumRow = i Exit Function End If Next i FindFirstSumRow = ws.Rows.Count + 1 End Function ``` 在上述代码中，首先定义了两个工作表对象 `wsWNote` 和 `wsCheck`，分别表示 `w 附注` 工作表和 `附注校验` 工作表。然后通过循环遍历 `w 附注` 工作表中的每一行，查找指定的文本。根据其上一行的内容判断是期初余额还是期末余额，确定复制和粘贴的起始行和结束行，并进行复制和粘贴操作。`FindFirstSumRow` 函数用于查找第一个包含 "合计" 的行。 在实际使用前，请根据您的工作簿结构和数据进行适当的调整和测试。 ######[AI写代码神器 | 830点数解答 | 2024-10-14 16:44:18]