excel - Excel VBA 对帐

Question

我有 excel 工作簿，如果 B 列和 W 列数据相同，我需要比较 B 列和 W 列需要将整行复制到新工作表（工作表名称“Reconciled”）B 列数据是这样的日期格式（2020-02 -01 07:55:08.0) W 列日期格式是这样的 (27/01/2020) B & W 列需要与日期进行比较。
此代码日期已选择，但它正在工作，但它是错误的。

Sub runThrough(cbpath As String, bspath As String)

Dim  newcashBook, newbankstmt As Worksheet
Dim cashbook, Bankstmt As Workbook
Dim i, j As Long
Dim cbRecords, bsRecords rng As String

Set cashbook = Workbooks.Open(cbpath)

   'copy data from another workbook
 Set newcashBook = cashbook.Sheets(1) 
 newcashBook.Range("A1:Z1048576").Copy
 cashbook.Close

      'paste data to W1 row from another workbook
    Set newbankstmt = ThisWorkbook.Sheets("Sheet0") 
    newbankstmt.Range("W1").PasteSpecial


 For i = 2 To 100
  Set newbankstmt = ThisWorkbook.Sheets("Sheet0")
  ' Sheet0 is activeworkbook active worksheet
   Rows.Cells(i, 2).Select

  Rows.Cells(i, 2).Select
      For j = 2 To 100 

        Rows.Cells(j, 31).Select

                 If (i = j) Then
                    Debug.Print "yes"   'check data same or not
                 Else
                    Debug.Print "wrong"

                 End If
 Next j
  Next i
End Sub

标签： excelvbanested-loops

Answer 1

我根据从您的问题和评论中推断出的内容准备了一个代码。因此，代码尽可能快地（使用数组）复制cashbook.Sheets(1)in的内容newbankstmt.Range("W1")。

然后它在 100 行之间迭代，如果特定行上的“B”单元格日期与同一行的“W”单元格日期相等，则在立即窗口中返回相应行的愤怒“A:W”地址并且代码停止了。您可以按 F5 转到下一个此类事件。为了在立即窗口中查看返回值，您必须按 Ctrl + G。

如果这是您需要的，请确认，我将向您展示如何将这些范围也加载到另一个数组中并立即粘贴到新工作表中，或者您需要的任何位置，如果清楚地说明在哪里...

此代码不关心单元格（日期）格式。但是，只有在讨论中的两个单元格（B 和 W）都是 Date 类型时，该代码才有效。

Sub runThrough(cbpath As String, bspath As String)
 Dim newcashBook As Worksheet, newbankstmt As Worksheet
 Dim cashbook As Workbook, Bankstmt As Workbook
 Dim i As Long, dateB As Date, dateW As Date, arrC As Variant

 Set cashbook = Workbooks.Open(cbpath)
   'copy data from cashbook:
   Set newcashBook = cashbook.Sheets(1)
   'input the big range in arrC array:
   arrC = newcashBook.Range("A1:Z1048576").value
 cashbook.Close

    'copy the arrC content starting from W1:
    Set newbankstmt = ThisWorkbook.Sheets("Sheet0")
    newbankstmt.Range("W1").Resize(UBound(arrC, 1), UBound(arrC, 2)).value = arrC


 For i = 2 To 100 'why To 100?
    dateB = newbankstm.Cells(i, "B").value
    dateW = newbankstm.Cells(i, "W").value
    If DateSerial(Year(dateB), Month(dateB), Day(dateB)) = DateSerial(Year(dateW), Month(dateW), Day(dateW)) Then
        Debug.Print "Range to be copied: " & newbankstm.Range(newbankstm.Cells(i, 1), _
                                                    newbankstm.Cells(i, "W")).Address
        Stop
    End If
 Next i
End Sub