CODEWITHSUNDEEP
vbaexcel
MaxAttack102
MaxAttack102

Reputation: 53

Check Two Different Rows, but Same Column

I am trying to get Excel to look at two different rows in two different sheets but the same column to validate the movement of the numbers in a separate column. How can I get rid of the current loop that I have so that it reduces the loading time? Because as of now it takes too long. Also, I know that I should not be using loops, I used it in low hopes that it would work, just as fine 

Sub repeatingrows()

Dim oldsheet As Worksheet
Dim newsheet As Worksheet
Set newsheet = Application.ActiveSheet

Set oldsheet = ThisWorkbook.Worksheets(3)
Set newsheet = ThisWorkbook.Worksheets(2)


'CHECK IF THE ROWS ARE SIMILAIR TO PREVIOUS SHEET

Dim rrow As Integer
Dim srow As Integer

For rrow = 3 To 397
    For srow = 3 To 397
        If oldsheet.Cells(rrow, 2) = newsheet.Cells(srow, 2) Then
            If oldsheet.Cells(rrow, 5) = newsheet.Cells(srow, 5) Then
                If oldsheet.Cells(rrow, 6) = newsheet.Cells(srow, 6) Then
                    With newsheet
                        oldsheet.Range(oldsheet.Cells(rrow, 16), oldsheet.Cells(rrow, 19)).Copy
                        .Range(.Cells(srow, 16), .Cells(srow, 19)).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
                    End With '^COPY AND PASTES THE ROW
                Else
                End If
            Else
            End If
        Else
        End If
    Next srow
Next rrow

End Sub

Upvotes: 0

Views: 68

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Get rid of objects! Accessing object members can be time consuming. Writing to objects worst. So you can cache read and writes, and get big time. Also, avoid copy/past, use .Value=...

Sub repeatingrows()

    Dim oldsheet As Worksheet
    Dim newsheet As Worksheet
    Set newsheet = Application.ActiveSheet

    Set oldsheet = ThisWorkbook.Worksheets(3)
    Set newsheet = ThisWorkbook.Worksheets(2)

    Dim oldv, newv, c

    'CHECK IF THE ROWS ARE SIMILAIR TO PREVIOUS SHEET

    Dim rrow As Integer
    Dim srow As Integer

    oldv = oldsheet.Range(oldsheet.Cells(1, 1), oldsheet.Cells(397, 19))
    newv = newsheet.Range(newsheet.Cells(1, 1), newsheet.Cells(397, 19))

    For rrow = 3 To 397
        For srow = 3 To 397
            If oldv(rrow, 2) = newv(srow, 2) And oldv(rrow, 5) = newv(srow, 5) And oldv(rrow, 6) = newv(srow, 6) Then
                For c = 16 To 19 'can't use range1.Value = range2.Value in VB arrays
                    newv(srow, c) = newv(srow, c) + oldv(rrow, c)
                Next
            End If
        Next
    Next

    'Finally, write results
    newsheet.Range(oldsheet.Cells(1, 1), oldsheet.Cells(397, 19)) = newv

End Sub

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

At the beginning of the program you have to store data from two sheets to two arrays. Then all the logic you will do using cached arrays, instead of referreing to the value of cells thousands of times (that's quite "expensive" operation).

Upvotes: 0

Related Questions