Loop within a loop

owen4512

Board Regular
Joined
Dec 10, 2014
Messages
71
Hi all,

I've created the below vba however it's not quite working how i would like it to. I'm looking to use value in "N11" to search rows of data in sheet2, copy the data if it matches "N11" and paste that on sheet3. The issue i'm having is when pasting on sheet3 the lastrow doesnt work so the data keeps pasting into row 2 and overriding the previous result.

Any help would be much appreciated :)

VBA Code:
Sub Search()

    Dim i_D, i_A, last_row_Data, last_row_Active As Integer
    Dim Search, Records As Long
   
    Search = Sheet1.Range("N11").Value
 
    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    last_row_Active = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1 'Finds last row on 'Active Data' tab
    
    For i_A = 2 To last_row_Active
    For i_D = 2 To last_row_Data
        If Sheet2.Range("B" & i_D).Value = Search Then
           
            Records = Records + 1
                Sheet2.Activate
                Sheet2.Range(Cells(i_D, 1), Cells(i_D, 14)).Copy
                
                Sheet3.Activate
                Sheet3.Range(Cells(i_A, 1), Cells(i_A, 14)).PasteSpecial xlPasteValuesAndNumberFormats

           
        Else
           
        End If
        
    
    Next i_D
    Next i_A
    
    Sheet1.Range("N13").Value = Records


End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Why the second loop?
VBA Code:
    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    For i_D = 2 To last_row_Data
        If Sheet2.Range("B" & i_D).Value = Search Then
            Records = Records + 1
            Sheet2.Range(Cells(i_D, 1), Cells(i_D, 14)).Copy
            last_row_Active = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1 'Finds last row on 'Active Data' tab
            Sheet3.Range(Cells(last_row_Active, 1), Cells(last_row_Active, 14)).PasteSpecial xlPasteValuesAndNumberFormats
        End If
     Next i_D
 
Solution

owen4512

Board Regular
Joined
Dec 10, 2014
Messages
71
Why the second loop?
VBA Code:
    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    For i_D = 2 To last_row_Data
        If Sheet2.Range("B" & i_D).Value = Search Then
            Records = Records + 1
            Sheet2.Range(Cells(i_D, 1), Cells(i_D, 14)).Copy
            last_row_Active = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1 'Finds last row on 'Active Data' tab
            Sheet3.Range(Cells(last_row_Active, 1), Cells(last_row_Active, 14)).PasteSpecial xlPasteValuesAndNumberFormats
        End If
     Next i_D

Thank you for this - I have tried it and initially it didnt work but i added in Sheet2.Activate & Sheet3.Activate and this now works perfectly. Thanks again :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top