Nested For Next Loop

[jimmyvba]

Hi,

I am new to the vba world and would really appreciate help with this problem. I am basically picking up a security from a sheet through a loop and pasting it on the 2nd sheet. After some minor calculations, I am running another loop on the second sheet looking for "Yes" and pasting the results back on the first sheet. I need to move on to the 1st loop after that and pick up the second security.
My current nested loop code doesn't seem to be working and I am not being able to figure out why. Have pasted the code for reference.
Thanks in advance.

Sub Pickup_Scanner()
Dim i, j As Integer
Sheets("Universe").Select
For i = 4 To 20
Cells(i, 5).Select
Selection.Copy
Sheets("Scan").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    For j = 19 To 120
    If Cells(j, 16).Value = "Yes" Then
    Range(ActiveCell.Offset(0, -15), ActiveCell.Offset(0, -1)).Copy
    
    Sheets("Universe").Select
    Sheets("Universe").Range("G4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next j
    End If
Next i
    
End Sub

 

[Gary McMaster]

I can't test it but having the "Next j" and the "End If" in reverse order (highlighted in red) is a problem. The "End If" should be inside the loop.

Hope that helps.

Gary

Sub Pickup_Scanner()

Dim i, j As Integer
Sheets("Universe").Select
For i = 4 To 20
Cells(i, 5).Select
Selection.Copy
Sheets("Scan").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    For j = 19 To 120
    If Cells(j, 16).Value = "Yes" Then
    Range(ActiveCell.Offset(0, -15), ActiveCell.Offset(0, -1)).Copy
    
    Sheets("Universe").Select
    Sheets("Universe").Range("G4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next j
    End If
Next i
    
End Sub

 

[Snakehips]

Jimmy,

Gary is correct regarding the Next j / End If but I'm not sure that in itself will fix it.
For me there is some further confusion.
Eg confusion with Range object and the j loop just copies to G4 on Universe.

Can you give further verbal detail as to what you want this to achieve?

 

[jimmyvba]

Thanks Gary. I have corrected what you pointed out.
Tony, I am copying one security at a time from sheet "Universe" (i,5) on to the "Scan" sheet (G2). On the scan sheet, I want to run the j loop to find out the cells marked "Yes" in (j,16) and copy some cells (using offset) for each j that satisfies the if condition back on to the "Universe" sheet. Once I have checked for j from 19 to 120, I would like to move on to the next i (so basically the next security) and repeat the process till i gets to 20.
Also as you pointed out, pasting it on G4 everytime is not the goal. I want to paste the results on G4 and the next one on G5 and so on.
Does this help?
Appreciate the inputs.

 

[ukmikeb]

Hi

May I point out that within each For ......Next you select another sheet so that the next time you go through the loop you are not referencing the sheet you expect to be referencing.

I suggest that you define a Worksheet Object for each of the sheets and use references to those rather than select, for example :-

Dim wsS as WorkSheet
Dim wsU as Worksheet

Set wsS = Worksheets("Scan")
Set wsU = Worksheets("Universe")

wsU.Cells(i, 5).Copy
wsS.Range("G2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

replaces the following -
Cells(i, 5).Select
Selection.Copy
Sheets("Scan").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

hth

 

[jimmyvba]

Thanks a lot Mike. Excellent point. Making that change right away.

 

[Snakehips]

Jimmy, I'm back, still confused but willing to help if I can.

Treat me as an idiot. Please confirm / correct.


- Start of i loop
Copy Univ E4 to Scan G2.
-j loop
Check Scan P19 :P120 for "Yes"
For each found row, copy Scan A:O to Univ G4, G5, etc say down to G22
- end j loop

Next in i loop
Copy Univ E5 to Scan G2 ??
- j loop
Check Scan P19 :P120 for "Yes" Again? Will result in same rows as before?
Copy found rows to Univ Where? Starting at G23????

 

[jimmyvba]

Hey Tony..First of all if there is any idiot here, it's me!!

- Start of i loop
Copy Univ E4 to Scan G2.
-j loop
Check Scan P19 :P120 for "Yes"
For each found row, copy Scan A:O to Univ G4, G5, etc say down to G22
- end j loop

Next in i loop
Copy Univ E5 to Scan G2 ?? Yes
- j loop
Check Scan P19 :P120 for "Yes" Again? Yes. May or may not result in the same rows. That is why we are scanning for the rows in which "Yes" is present.
Copy found rows to Univ Where? Starting at G23???? Yes. Basically where the first available cell is. It would be G23 in your case as the previous loop that you ran pasted up to row 22. If the previous loop had pasted it upto G20, the next loop would paste it from G21 onwards.

I hope I have been able to clarify better this time. As I mentioned before, really appreciate the help.
Cheers!

 

[Snakehips]

Idiot One calling Idiot Two.....
Am I correct in thinking that the Yes's in P are the result of a formula that is using the value in G2?

 

[Snakehips]

Jimmy,
Give this a try.....

Sub xPickup_Scanner()
Dim i, j As Integer
Dim wsS As Worksheet
Dim wsU As Worksheet
Dim MyRng As Range
Set wsS = Worksheets("Scan")
Set wsU = Worksheets("Universe")
Set MyRng = wsU.Range("G4:U4")
For i = 4 To 20
wsS.Range("G2") = wsU.Cells(i, 5)
    For j = 19 To 120
    If wsS.Cells(j, 16).Value = "Yes" Then
      MyRng = wsS.Range("A" & j & ":O" & j).Value
      Set MyRng = MyRng.Offset(1, 0)
    End If
    Next j
Next i
    
End Sub