Nested for Loop

Arae97

New Member
Joined
Jun 4, 2019
Messages
7
I can't seem to figure out how to jump to the next "c" in the loop after pasting my data. it works the first time but does not return to the first loop to change the c value to continue the process. any help would be great

Sub CopyPasteData()
Dim Pool As Worksheet
Dim Family As Range
Dim i As Integer
Dim Finalrow As Integer
Dim c As Variant
Set Pool = Sheet1
Set Family = Pool.Range("R2:R16")
Pool.Select
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For Each c In Family


For i = 2 To Finalrow
If Cells(i, 1) = c.Value Then
Range(Cells(i, 2), Cells(i, 13)).Copy
Sheets(c.Value).Select
Range("a3").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Pool.Select
End If
Exit For

Next c


End Sub
[/code]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
yes I tried both thinking it may be a problem. thanks for your help but there is still a problem.
 
Upvote 0
You're missing a Next i
 
Upvote 0
Sub CopyPasteData()
Dim Pool As Worksheet
Dim Family As Range
Dim i As Integer
Dim Finalrow As Integer
Dim c As Range
Set Pool = Sheet1
Set Family = Pool.Range("R2:R16")
Pool.Select
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For Each c In Family


For i = 2 To Finalrow
If Cells(i, 1) = c.Value Then
Range(Cells(i, 2), Cells(i, 13)).Copy
Sheets(c.Value).Select
Range("a3").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Pool.Select
End If
Next i
Next c


End Sub

This is what i have changed to it. but it still doesn't change to the next c. it continues through the second loop not starting back at the first.
 
Upvote 0
Can you please explain what you mean by
it continues through the second loop not starting back at the first.
 
Upvote 0
when i step into the query to find the problem it doesn't change the c value to the next value in the range it remains the same.
 
Upvote 0
If you add this msgbox box
Code:
For Each c In Family
MsgBox c.Value

For i = 2 To Finalrow
Does the value change each time it opens?
 
Upvote 0
I feel like this is what you are looking for...

Code:
Sub CopyPasteData()
Dim i As Integer, c As Variant
    ThisWorkbook.Sheets("Sheet1").Activate
    For Each c In Range("R2:R16")
        For i = 2 To ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count
            If Cells(i, 1) = c.Value Then
                Range(Cells(i, 2), Cells(i, 13)).Copy
                Sheets(c.Value).Select
                Range("a3").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
                ThisWorkbook.Sheets("Sheet1").Activate
                Exit For
            End If
        Next i
    Next c
End Sub
 
Last edited by a moderator:
Upvote 0
no it did not change. it only showed the box once for the first value. when I step into it to run step by step it it never returns up past the "For i = 2 To Finalrow"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top