Read two columns using nested for loop

Question

I need to read two columns it's like an excel reconciliation, I need to capture column B and column W then column B check 1 record and match this record from W column if column B record and column W record match then it needs to move the new worksheet. anyone can check or advise me on how to do this nested for loop. and how to capture column B & W for nested For loop.

  For i = 2 To 20

   Rows.Cells(i, 2).Select
   Rows.Cells(i, 2).Select
   Rows.Cells(j, 31).Select
      Next j
   Next i

Note that you have to nested loops using the same increment variabe `For i = 1` that cannot work! Also count your `For` and `Next` statements, there needs to be the same amount. Every `For` needs a corresponding `Next`. — Pᴇʜ, Mar 11 '20 at 07:10
Note that if you declare `Dim i, j As Integer` only `j` is `Integer` but `i` is `Variant`. In VBA you need to specify a type for **every** variable: `Dim i As Long, j As Long`. Also I recommend to [use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) as there is no benefit in using `Integer` in VBA. • Same for all your other variables. — Pᴇʜ, Mar 11 '20 at 07:15
hi @Pᴇʜ, I need to know how to match column B and W column if both B & W data same those match data copy to a newsheet — Dinuka Thilakarathne, Mar 11 '20 at 09:15
But the code you showed cannot work because you need to fix that first. — Pᴇʜ, Mar 11 '20 at 09:16
I think that first `For i = 1 To cbRecords` should be commented out or deleted - it doesn't have a `Next i` (as well as it's using the same variable as the next loop as @Peh commented). — Darren Bartrup-Cook, Mar 11 '20 at 09:27
now it's working with nested for loop now I need to know hot compare column B & W. but I don't have an idea about how to compare both columns and copy to new worksheet. so can you help me? — Dinuka Thilakarathne, Mar 11 '20 at 09:30
What does `cbRecords` contain? You're using a `RecordCount` on it. It's declared as a `Variant`, although I guess you meant a `String` - which wouldn't have a `RecordCount` property - and it has no value. You declare it and then try and pull values from it - where do the values come from? Then there's `Set cashbook = Workbooks.Open(cbpath)` - `cbPath` contains **"D:\Union Bank Mcash Rec\CB\168022-today"** which is only a path, not a path and filename so it should fail there to. — Darren Bartrup-Cook, Mar 11 '20 at 09:40
@DarrenBartrup-Cook, I updated my code in up, now i need compare both B & W column if match if both column details are a match then that full compare row copy to the new worksheet — Dinuka Thilakarathne, Mar 11 '20 at 09:55
Copy both rows that match to new worksheet, or some columns from each into one new row. ? — CDP1802, Mar 11 '20 at 10:58
@CDP1802 first check column B and second check column W if column B details are matched to column W (column B details & column W details are same) then already match column W details need to copy to a new worksheet — Dinuka Thilakarathne, Mar 11 '20 at 11:48
So if B10 matches W20 you want the entire row 20 copied (including columns A to V) ? What is the matching cell types date, string, number, integer ? — CDP1802, Mar 11 '20 at 11:59
@CDP1802 Dim newcashBook, newbankstmt As Worksheet Dim cashbook, Bankstmt As Workbook Dim i, j As Integer (yes i need to copy A to V ) — Dinuka Thilakarathne, Mar 11 '20 at 12:01

score 0 · Answer 1 · answered Mar 11 '20 at 11:03

Try the code below. It will compare columns B & W. If they're the same the whole row is copied to the second worksheet. ThisWorkbook refers to the book that contains the code.

Public Sub Test()

    Dim SheetToCheck As Worksheet, SheetToPasteTo As Worksheet
    Dim lRow As Long
    Dim NextEmptyRow As Long

    Set SheetToCheck = ThisWorkbook.Worksheets("Sheet1")
    Set SheetToPasteTo = ThisWorkbook.Worksheets("Sheet2")

    For lRow = 2 To 20
        If SheetToCheck.Cells(lRow, 2) = SheetToCheck.Cells(lRow, 23) Then 'Compare Col B to Col W.
            NextEmptyRow = SheetToPasteTo.Cells(Rows.Count, 2).End(xlUp).Row + 1
            SheetToCheck.Rows(lRow).Copy Destination:=SheetToPasteTo.Rows(NextEmptyRow)
        End If
    Next lRow

End Sub

score 0 · Answer 2 · answered Mar 11 '20 at 12:21

I'm not sure I fully understand what you are trying to achieve but try this

Option Explicit

Sub runThrough(cbpath As String, bspath As String)

    Dim wbSource As Workbook, wsSource As Worksheet
    Dim wb As Workbook, ws As Worksheet, wsMatch As Worksheet
    Dim iLastRow As Long, iLastRowB As Long, iTargetRow As Long
    Dim t0 As Single, count As Long, matches As Long
    t0 = Timer

    ' cash book
    Set wbSource = Workbooks.Open(cbpath, False, True) ' no link update, read only
    Set wsSource = wbSource.Sheets(1)
    iLastRow = wsSource.Range("A" & Rows.count).End(xlUp).Row

    ' summary
    Set wb = Workbooks.Open(bspath)
    Set ws = wb.Sheets(1)
    iLastRowB = ws.Range("B" & Rows.count).End(xlUp).Row

    ' copy from cashbook to summary
    wsSource.Range("A1:Z" & iLastRow).Copy ws.Range("W1")
    wbSource.Close SaveChanges:=False

    ' create new sheet for matched rows
    Set wsMatch = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.count))
    wsMatch.Name = "matched"

    ' nested loop
    Dim i As Long, j As Long
    Dim colB As String, colW As String
    iTargetRow = 0
    For i = 2 To iLastRowB
       colB = ws.Cells(i, "B")
       For j = 1 To iLastRow
           colW = ws.Cells(j, "W")
           If colB = colW Then
               ' do something
               iTargetRow = iTargetRow + 1
               ws.Rows(j).EntireRow.Copy wsMatch.Cells(iTargetRow + 1, 1)
           End If
           count = count + 1
       Next
    Next

    ' result
    MsgBox count & " iterations " & iTargetRow & "  rows copied to new sheet " & wsMatch.Name, _
           vbInformation, "Completed in " & Int(Timer - t0) & " secs"

End Sub

Read two columns using nested for loop

2 Answers2