Next For Each Statements and Improving Nested For Loops

[jthaney10]

I have two spread sheets. Look up a value from spreadsheet A, column A and search for it in column A of spreadsheet B. If a match is found copy data from spreadsheet A to spreadsheet B. If no match is found, look up a value from spreadsheet A, column B and search for it in column B of spreadsheet B.

I have the following code that I would like to hopefully speed up.

Option Explicit

'Copy data from Lateral Details
'To MASTER if the headers match
Sub CopyData()
    
    'DEFINE VARIABLES
    Dim wsMASTER As Worksheet, wsLatDetails As Worksheet
    Dim FPLID As Range, foundFPLID As Range
    Dim i As Long, j As Long
    Dim LastRowMASTER As Long, LastRowLat As Long
    
    'SPEEDS UP MACRO---START
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    
    'SET WORKSHEETS
    Set wsMASTER = Worksheets("MASTER")
    Set wsLatDetails = Worksheets("Lateral Details Final")
    
    'FIND LAST ROWS
    LastRowMASTER = wsMASTER.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    LastRowLat = wsLatDetails.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    'FOR LOOP TO CYCLE THROUGH COLUMN DATA
    For i = 3 To LastRowMASTER

        For j = 2 To LastRowLat
            'FIND FPL ID MATCH AND COPY DATA
            If wsMASTER.Cells(i, 4).Value = wsLatDetails.Cells(j, 4).Value Then
                wsLatDetails.Range(wsLatDetails.Cells(j, 7), wsLatDetails.Cells(j, 13)).Copy _
                    wsMASTER.Cells(i, 10) 'FIND FPLID MATCH
        Exit For
            'FIND TLN MATCH AND COPY DATA
            ElseIf wsMASTER.Cells(i, 6).Value = wsLatDetails.Cells(j, 5).Value Then
                wsLatDetails.Range(wsLatDetails.Cells(j, 7), wsLatDetails.Cells(j, 13)).Copy _
                    wsMASTER.Cells(i, 10) 'FIND TLN MATCH
        Exit For
            
            End If

        Next j

    Next i
    
'SPEEDS UP MACRO---FINISH
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    
End Sub

I wrote this code that can handle looking in one column, but was unsuccessful move to the the next and nesting another for each.

    'SEARCHES FOR MATCHING FPL ID, IF FOUND COPY CORRESPONDING CELLS
    For Each FPLID In wsMASTER.Range(wsMASTER.Cells(3, 4), wsMASTER.Cells(LastRow, 4)) 'DESTINATION
        Set foundFPLID = wsLatDetails.Columns(4).Find(FPLID, LookIn:=xlValues, LookAt:=xlWhole) 'SOURCE
            If Not foundFPLID Is Nothing Then
                wsLatDetails.Range(wsLatDetails.Cells(foundFPLID.Row, 7), wsLatDetails.Cells(foundFPLID.Row, 13)).Copy _
                    wsMASTER.Cells(FPLID.Row, 9) 'SOURCE TO DESTINATION
            End If
    Next FPLID

 

[Mentor82]

Hi,
I've got a couple of questions:
1. Do you need just to copy values from on worksheet to another if found of formatting as well? The reason why I'm asking is if you need just values then it's required to consider using loop in arrays, not in cell and then rewrite the values that you need if the match is found. Loop in arrays is done in RAM and it's much more efficient to loop in ranges.
2. If you need to copy formating as well then we've got we can still use loop in arrays but the copy metod will be done using ranges which is not efficient but still better then looping through worksheet ranges

To write you the code using arrays I would need to know if the used range in wsMaster includes the columns numbers to which you paste data or are these columnst 10-16, where you paste data in, additional ones, out of usedrange?

regards,
Sebastian

 

[jthaney10]

Hi,
I've got a couple of questions:
1. Do you need just to copy values from on worksheet to another if found of formatting as well? The reason why I'm asking is if you need just values then it's required to consider using loop in arrays, not in cell and then rewrite the values that you need if the match is found. Loop in arrays is done in RAM and it's much more efficient to loop in ranges.
2. If you need to copy formating as well then we've got we can still use loop in arrays but the copy metod will be done using ranges which is not efficient but still better then looping through worksheet ranges

To write you the code using arrays I would need to know if the used range in wsMaster includes the columns numbers to which you paste data or are these columnst 10-16, where you paste data in, additional ones, out of usedrange?

regards,
Sebastian

Thanks for the reply Sebastian.

1. The formatting will be a mixture of strings and numbers, so I would say yes that I need to keep formatting. I also could do with just pasting however, and edit after.

The columns exist in the Master already. I just need to fill in the cells.