Resetting Row Counter Loop?

[TycExcel]

I have this code here that copies, and pastes from one sheet to another if the match is not found. However, I cant seem to get the row counter to reset to the top of the row. Once it is done looping through a row, it should reset to the second row (as the first row will be a header row). Furthermore, if the code finds a match on any of the new sheets it has to delete the existing entry, and replace it in the sheet that I am pasting to. I have my code below. Can someone please help? Thanks.

Sub FindMissingProductsAndCopyThem1()
    Dim ws1 As Worksheet
    
    Dim ws2 As Worksheet
    
     'define which worksheet
    Set ws2 = ThisWorkbook.Worksheets("BOM")
    
    Dim lRowSource As Long

    Dim lRowDestination As Long
    
    lRowDestination = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).row 'find last row in column D
    
    Dim FoundRow As Long

    Dim iRow As Long
    
    Dim iCol As Long
    
    
    
    For Each ws1 In ActiveWorkbook.Worksheets
    
        If ws1.Name Like "Revision *" Then
      
        lRowSource = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).row 'find last row in column A
        lColumnSource = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
           
            For iCol = 1 To lColumnSource
                For iRow = 2 To lRowSource
                    
                    FoundRow = 0 'initialize/reset
                    
                    On Error Resume Next 'next line throws an error if not matched, catch that error
        'try to find/match the data of column A in column D
                    FoundRow = Application.WorksheetFunction.Match(ws2.Cells(iRow, iCol), ws1.Columns(iCol), 0)
                    On Error GoTo 0 're-activate error reporting
                    
        'if Match threw an error, then FoundRow is still 0

                    If FoundRow = 0 Then 'product was not found, so add it
                        c = Columns(iCol).Column
                        lRowDestination = lRowDestination + 1
                        ws2.Cells(lRowDestination, iCol).Value = ws1.Cells(iRow, iCol)
                        
                        
                       
                        'If iCol = iCol - 1 Then
                            'lRowDestination = Cells(Rows.Count, c).End(xlUp).row
                         
                         'End If
                        
                     End If
                     
                     
                        
                        
                        
                Next iRow
             
            Next iCol
            
        End If
        
    Next ws1
   
End Sub

 

[Joe4]

I find your question a bit confusing (and guessing that I am not alone, due to the lack of replies).

I think it would be more helpful to us if you show us a small sample of your data, and what you want the expected results of that data to look like (being sure to include all important details, like where this list we are looking for is also found and what it looks like).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[TycExcel]

I find your question a bit confusing (and guessing that I am not alone, due to the lack of replies).

I think it would be more helpful to us if you show us a small sample of your data, and what you want the expected results of that data to look like (being sure to include all important details, like where this list we are looking for is also found and what it looks like).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Joe,

Thanks for the response. I basically need a loop that that will go through each column and row on other sheets, and paste the values that are not found on the summary sheet to the summary sheet. If the value exists on the summary sheet, already, it needs to be overwritten with the latest data.