Need Help Writing VBA Loop

therber2 · Oct 31, 2016

Folks, in plain English right quick, here is what I am DOING and then I'll show the vba code for it:
I have a Range from N2:AE2 > I want vba to autofill the forumulas in that range all the way down from 2 to row 198
Caveat, it is web scraping so it needs to autofill N rows every N seconds. I wrote the script in NON-LOOP format as such:

Code:

    Range("N2:AE2").Select
    Selection.AutoFill Destination:=Range("N2:AE10"), Type:=xlFillDefault
        Calculate
    Application.Wait (Now + #12:00:12 AM#)
        If Not Application.CalculationState = xlDone Then
    DoEvents
End If


    Range("N10:AE10").Select
    Selection.AutoFill Destination:=Range("N10:AE20"), Type:=xlFillDefault
        Calculate
    Application.Wait (Now + #12:00:12 AM#)
        If Not Application.CalculationState = xlDone Then
    DoEvents
End If


    Range("N20:AE20").Select
    Selection.AutoFill Destination:=Range("N20:AE30"), Type:=xlFillDefault
        Calculate
    Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If
    
    Range("N30:AE30").Select
    Selection.AutoFill Destination:=Range("N30:AE40"), Type:=xlFillDefault
        Calculate
   Application.Wait (Now + #12:00:12 AM#)
 If Not Application.CalculationState = xlDone Then
    DoEvents
End If
  
       Range("N40:AE40").Select
    Selection.AutoFill Destination:=Range("N40:AE50"), Type:=xlFillDefault
        Calculate
   Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If
   
       Range("N50:AE50").Select
    Selection.AutoFill Destination:=Range("N50:AE60"), Type:=xlFillDefault
        Calculate
   Application.Wait (Now + #12:00:12 AM#)
 If Not Application.CalculationState = xlDone Then
    DoEvents
End If
  
       Range("N60:AE60").Select
    Selection.AutoFill Destination:=Range("N60:AE70"), Type:=xlFillDefault
        Calculate
   Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N70:AE70").Select
Selection.AutoFill Destination:=Range("N70:AE80"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N80:AE80").Select
Selection.AutoFill Destination:=Range("N80:AE90"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N90:AE90").Select
Selection.AutoFill Destination:=Range("N90:AE100"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N100:AE100").Select
Selection.AutoFill Destination:=Range("N100:AE110"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N110:AE110").Select
Selection.AutoFill Destination:=Range("N110:AE120"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N120:AE120").Select
Selection.AutoFill Destination:=Range("N120:AE130"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N130:AE130").Select
Selection.AutoFill Destination:=Range("N130:AE140"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N140:AE140").Select
Selection.AutoFill Destination:=Range("N140:AE150"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N150:AE150").Select
Selection.AutoFill Destination:=Range("N150:AE160"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N160:AE160").Select
Selection.AutoFill Destination:=Range("N160:AE170"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N170:AE170").Select
Selection.AutoFill Destination:=Range("N170:AE180"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N180:AE180").Select
Selection.AutoFill Destination:=Range("N180:AE190"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If


Range("N190:AE190").Select
Selection.AutoFill Destination:=Range("N190:AE200"), Type:=xlFillDefault
    Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
    DoEvents
End If




End Sub

QUESTION: How can I make this autofill every 10 rows per 12 seconds a LOOP with the ability to easily change (for optimizing the scrape [im using the smf excel add in by the way with its 'rchgettablecells' formula contained in each cell of the range]) those per row and per second parameters?

Thank you!
Tom

Brian F · Oct 31, 2016

I'm not clear on what you're doing but; The 1st code seems like it does not follow the pattern of the later code so I used it as is. This loops starting with the second group. Try it in your workbook

Sub DoSomething()

Dim i As Integer

Range("N2:AE2").Select
Selection.AutoFill Destination:=Range("N2:AE10"), Type:=xlFillDefault
Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
DoEvents
End If

For i = 10 To 30 'Change 30 to 80 or 1000 depending on how many times you want the code to repeat. "10 to 30" causes it to loop 3 time. "10 to 50" causes it to loop 5 time.
Range("N" & i & ":AE" & i).Select
Selection.AutoFill Destination:=Range("N" & i & ":AE" & 10 + i), Type:=xlFillDefault
Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
DoEvents
End If
i = i + 10
Next i
End Sub

therber2 · Oct 31, 2016

Brian F said:
I'm not clear on what you're doing but; The 1st code seems like it does not follow the pattern of the later code so I used it as is. This loops starting with the second group. Try it in your workbook

Sub DoSomething()

Dim i As Integer

Range("N2:AE2").Select
Selection.AutoFill Destination:=Range("N2:AE10"), Type:=xlFillDefault
Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
DoEvents
End If

For i = 10 To 30 'Change 30 to 80 or 1000 depending on how many times you want the code to repeat. "10 to 30" causes it to loop 3 time. "10 to 50" causes it to loop 5 time.
Range("N" & i & ":AE" & i).Select
Selection.AutoFill Destination:=Range("N" & i & ":AE" & 10 + i), Type:=xlFillDefault
Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
DoEvents
End If
i = i + 10
Next i
End Sub

Thanks Brian, looks like it just needs some small fix. After the first loop, the selected range just moves down in given increments, but stop autofilling.
A good start! Thank you!

therber2 · Nov 1, 2016

It works now if I change the line:

Code:

[COLOR=#333333][I]Selection.AutoFill Destination:=Range("N" & i & ":AE" & 10 + i), Type:=xlFillDefault[/I][/COLOR]

to

Code:

[COLOR=#333333][I]Selection.AutoFill Destination:=Range("N" & i & ":AE" & 11 + i), Type:=xlFillDefault[/I][/COLOR]

Honestly doesn't make sense to me why it would do that, but works.

Any idea how to get it to stop on a precisce row? Say, row 198?

Brian F · Nov 2, 2016

As I said, it isn’t clear to me what you are trying to do and what the significance of “line 192” is. It appears the after running the fist code section and repeating the second one 19 times it ends up at line 192. Is that correct? This does that.

Sub DoSomething2()
Dim i As Integer
Dim n As Integer
Dim x As integer

X = 19
I = 10

Range("N2:AE2").Select
Selection.AutoFill Destination:=Range("N2:AE10"), Type:=xlFillDefault
Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
DoEvents
End If

For n= 1 To x
Range("N" & i & ":AE" & i).Select
Selection.AutoFill Destination:=Range("N" & i & ":AE" & 11 + i), Type:=xlFillDefault
Calculate
Application.Wait (Now + #12:00:12 AM#)
If Not Application.CalculationState = xlDone Then
DoEvents
End If
i = i + 10
Next n
End Sub

therber2 · Nov 17, 2016

Brian F said:
 As I said, it isn’t clear to me what you are trying to do and what the significance of “line 192” is. It appears the after running the fist code section and repeating the second one 19 times it ends up at line 192. Is that correct? This does that.

Indeed. Sorry to have been vague there Brian, and I really appreciate the help here.
I am going to give this a few test runs. I am learning a lot about loops as well thanks to you

therber2 · Nov 17, 2016

therber2 said:
Indeed. Sorry to have been vague there Brian, and I really appreciate the help here.
I am going to give this a few test runs. I am learning a lot about loops as well thanks to you

Clarification as to WHY I am doing this: are you familiar with the smf plugin (third party stock market quote xmlhttp get request fetcher)?
I use it's function: '=rchgettablecell()' to grab specific data parsed through cell formulas and compiled afterward with an array for sports stats (I am a sports writer).

There were 3 issues/considerations when first writing the initial (non-loop) code:

1. Time each query takes to both connect to the web url and then for excel to run its calculations. It is pretty variable based on speed of my internet (which varies for whatever reason) and the site's (singular) page load times which also varies by a number of factors: one of them being, the complexity of the query on said site's server (Note: I DO indeed have written permission to do this).

2. 500 errors: If you open more than about 10 url's on the domain at once(? or in a time frame maybe?), you'll get a 500 error. I asked the site owner if I could pay him to whitelist my account....and I did indeed pay him to compensate him for possibly increasing his cloud server costs and he said I have a longer timeout now, but the 500 error still happens. That is why I wrote in the pause between fetching batches (12 seconds after each batch of 10 seems to be the sweet spot, but I want to use this loop now to optimize that, and play around with doing 1 query per loop.

3. Not having the loops overlap: That is, if one is taking an unusually long time, the next loop won't start until the last one is done. As you saw, I put in the code:

Code:

[COLOR=#333333]If Not Application.CalculationState = xlDone Then[/COLOR]
[COLOR=#333333]DoEvents[/COLOR]
[COLOR=#333333]End If[/COLOR]

...but it doesn't seem to work. Not sure why, but I think 'CalculationState' won't take an xmlhttp get request process into account as a 'calculation' -- Do you guys know how I might be able to clarify that code to say If BOTH excel application calculations AND web xmlhttp get requests are done, then begin next loop?

Maybe it is the "DoEvents" thing that isn't counting web queries as "Events"

Thanks guys

PS. I'm not a super experienced excel user so I appologize if I used incorrect or confusing terminology. If I did, just ask me what I meant, and I can clarify. I think I got most of it right

therber2 · Nov 17, 2016

therber2 said:
Clarification as to WHY I am doing this: are you familiar with the smf plugin (third party stock market quote xmlhttp get request fetcher)?
I use it's function: '=rchgettablecell()' to grab specific data parsed through cell formulas and compiled afterward with an array for sports stats (I am a sports writer).

Here is the documentation for the plugin in case that helps (or if anyone is interested, it is a pretty awesome free plugin):
RCHGetTableCell-Function.html

Need Help Writing VBA Loop

therber2

New Member

Excel Facts

Brian F

Active Member

therber2

New Member

therber2

New Member

Brian F

Active Member

therber2

New Member

therber2

New Member

therber2

New Member

Similar threads

Forum statistics

Share this page

Need Help Writing VBA Loop

New Member

Excel Facts

Active Member

New Member

New Member

Active Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock