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

 

[jimmyvba]

Hey Tony. Thanks for the help once again. I think your code works absolutely fine.

However, I have hit one last roadblock.

You are right in assuming that the Yes's in P are the result of formulas that are using the value in G2. In fact I am downloading data from bloomberg for the security fed into G2 everytime. Then based on certain criteria I am marking the Yes's. However the problem that I am facing now is that the loop goes forward before the data is completely downloaded. Hence, there are no Yes's to begin with. In the present form, the code works only when I run it once when the data for a given security is completely downloaded.

How do I ensure that bloomberg data has been completely downloaded in my own vba code before moving on to the next line in the code. I know this question is not directly related to my initial question but if you happen to have any thoughts on it do let me know. This is what I have added on to your code as of now -
Cell C7 on my Scan sheet checks if the sheet is ready or not (if all data has been downloaded completely).
Set MyRng1 = wsS.Cells(7, 3)
If MyRng1 = "REQUESTING DATA..." Then
ActiveSheet.Calculate
ActiveSheet.Calculate
Application.Wait (Now + TimeValue("0:00:10"))
End If

The above input from me however is not doing the job currently.

Appreciate all the help. You guys are awesome!

Cheers.

 

[ukmikeb]

Hi

When you are retrieving your web page do you have code like this :-

    Do Until IE.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    
    Do Until IE.Document.readyState = "complete"
        DoEvents
    Loop

to ensure the web page is loading?

 

[jimmyvba]

Hey Mike,
I am retrieving data through Bloomberg API. Not sure about the web-page thing you are asking. I have access to Bloomberg terminal from which I am pulling in the data using Bloomberg functions like BDP and BDH. Not sure, whether this answers your question.
Cheers.

 

[ukmikeb]

Hi

Ok, in Post #11 you check C7 for "REQUESTING DATA ..." what do you expect in that cell once the request has been satisfied?

So then the code might be like :-

    Do 
    Loop Until wsS.Cells(7, 3) <> "REQUESTING DATA ..."

hth