Nested For Next Loop

jimmyvba

New Member
Joined
Jul 19, 2013
Messages
20
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.
Code:
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

Rich (BB code):
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

Well-known Member
Joined
May 17, 2009
Messages
4,734
Office Version
2013
Platform
Windows
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

New Member
Joined
Jul 19, 2013
Messages
20
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

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
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 :-
Code:
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
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,734
Office Version
2013
Platform
Windows
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

New Member
Joined
Jul 19, 2013
Messages
20
Hey Tony..First of all if there is any idiot here, it's me!! :LOL:

- 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

Well-known Member
Joined
May 17, 2009
Messages
4,734
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
May 17, 2009
Messages
4,734
Office Version
2013
Platform
Windows
Jimmy,
Give this a try.....

Code:
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
 

Forum statistics

Threads
1,082,316
Messages
5,364,491
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top