if and for loop contained inside while loop VBA

Question

I am attempting to write some code that loops throw a column of data in one column ad matches it with data in another column all in the same sheet. When the two data points are matched the corresponding data will be copied to beside the first data point. The simplest way of putting it is I have a if statement inside a for Staten inside a while loop. I believe the issue is I am either not while looping correctly or I am not assigning the data correctly, either way the script is not writing any data to the columns they or supposed to write to. Any help in getting this script working would be appreciated see code below.

Sub s()
    Dim i As Integer
    Dim pointer As Integer

    pointer = 1

    Do While ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 13) <> ""

        For i = 1 To 305
            If ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 1).Value = 
        ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 13).Value Then

                ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 14).Value = ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 2).Value

                ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 15).Value = ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 3).Value
            End If
            pointer = pointer + 1
        Next i
    Loop
End Sub

You know this goes row by row matching. So row 1 on one sheet will need to match row 1 on the second, row 2 to row 2 and so on. It is not searching the column in full? — Scott Craner
– Scott Craner, Commented Jun 28, 2018 at 18:38
If ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 1).Value = _ <~ line continuation is mandatory if the instruction is going to be continued on the next line — Mathieu Guindon
– Mathieu Guindon, Commented Jun 28, 2018 at 18:38
Do you have to do this with VBA? It sounds like Index/Match could do this very easily. — BigBen
– BigBen, Commented Jun 28, 2018 at 18:38
Actually move: pointer = pointer + 1 outside the for loop. But I would still recommend for speed the use of variant arrays. — Scott Craner
– Scott Craner, Commented Jun 28, 2018 at 18:46

Scott Craner · Accepted Answer · 2018-07-10 13:15:23Z

3

Move pointer = pointer + 1 outside the For Loop

Sub s()
    Dim i As Long
    Dim pointer As Long

    pointer = 1
    Do While ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 13) <> ""
        For i = 1 To 305
            If ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 1).Value = ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 13).Value Then
                ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 14).Value = ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 2).Value
                ThisWorkbook.Sheets("MPACSCodesedited").Cells(pointer, 15).Value = ThisWorkbook.Sheets("MPACSCodesedited").Cells(i, 3).Value
            End If
        Next i
        pointer = pointer + 1
    Loop
End Sub

But as stated in my comments using variant arrays will be quicker:

Sub s()
    With ThisWorkbook.Worksheets("MPACSCodesedited")
        lastrw = .Cells(.Rows.Count, 13).End(xlUp).Row
        Dim outarr As Variant
        outarr = .Range(.Cells(1, 13), .Cells(.Cells(.Rows.Count, 13).End(xlUp).row,15)).Value

        Dim SearchArr As Variant
        SearchArr = .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count,1).End(xlUp).Row, 3))

        Dim i As Long
        For i = LBound(outarr, 1) To UBound(outarr, 1)
            Dim j As Long
            For j = LBound(SearchArr, 1) To UBound(SearchArr, 1)
                If SearchArr(j, 1) = outarr(i, 1) Then
                    outarr(i, 2) = SearchArr(j, 2)
                    outarr(i, 3) = SearchArr(j, 3)
                    Exit For
                End If
            Next j
        Next i

        .Range(.Cells(1, 13), .Cells(.Rows.Count, 14).End(xlUp)).Value = outarr
    End With
End Sub

edited Jul 10, 2018 at 13:15

answered Jun 28, 2018 at 18:48

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Profex Over a year ago

With 87,000 rows, that is still a lot of looping. For maximum speed, I'd add a row index to the 2D array (or just a separate pointer array), sort it, use a binary search instead of the double looping, re-sort it by row index when done and dump the array back.

user9730643 Over a year ago

@Scott Craner I am trying this script however it is not looping through all the values. Do you have any suggestions.

Scott Craner Over a year ago

Then one of the arrays are not grabbing everything. I have adjusted the first, but the second array, the 305 rows was set by you. Is that 305 a set number or is the data in columns A:C dynamic? @user9730643

user9730643 Over a year ago

@ScottCraner I hardcoded that value and now it is 891375 instead of 305

Scott Craner Over a year ago

@user9730643 well, I can only go off the information in the question. See edit. I have made it dynamic.

Collectives™ on Stack Overflow

if and for loop contained inside while loop VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related