0

So I have an issue, I created a loop to find where the sheet says "Standard Deviation" and for it to pull the data 3 cells to the right of it. The issue is this is supposed to pull data after the Query Table loads. My code won't work because it runs before the Querytable loads. 

Sub StdB5()
Dim URL As String
Dim ws As Worksheet
Dim qt As QueryTable
Dim mep As Worksheet
Dim Symbol As String
Set mep = Worksheets("Managed Equity Portfolios")
Set ws = Worksheets("Hidden Sheet 3")

Sheets("Hidden Sheet 3").Cells.ClearContents
Sheets("Hidden Sheet 3").Visible = True
Symbol = Symbol & mep.Range("B5").Value
 URL = "https://www.google.com/finance?q=MUTF:" + Symbol

Set qt = ws.QueryTables.Add( _
   Connection:="URL;" & URL, _
   Destination:=ws.Range("A1"))

qt.Refresh

Dim stdRange As Range
Dim cel As Range
Dim stdcell As Range 'std on MEP
Set stdcell = mep.Cells.Range("H5")   'std on MEP
Set stdRange = ws.Range("A45:A50")
For Each cel In stdRange
    If cel.Value = ("Standard deviation") Then
       stdcell.Value = cel.Offset(0, 3).Value
    End If
Next cel

Is there any way to make it so it waits until the Query Table loads before running? Instead of having to physically run the macro again after the table loads?

Edit: The reason I need to do this is because sometimes after the QT runs, the standard deviation will be in, for example, cell A45 instead of A47. This makes it so I can't just make the cell I want to populate ='Sheet2'A45' Edit 2: Added whole code

elguapo
  • 155
  • 1
  • 14
  • What query table? Maybe you need to expand on that a little? – Tim Williams Jul 25 '17 at 17:46
  • Sorry, I will add the full code in a second – elguapo Jul 25 '17 at 18:22
  • Possible duplicate: https://stackoverflow.com/questions/36341741/excel-vba-after-data-import/ – David Zemens Jul 25 '17 at 18:30
  • Specifically [this answer](https://stackoverflow.com/a/36342211/1467082) allows you to configure an event-handler for the QueryTable so that you can automatically execute some code/function/etc *after* it's refreshed. – David Zemens Jul 25 '17 at 18:32
  • Hey thanks for the reply, I just tried that but this makes it very slow since I have a lot of connections that are in this workbook. I tried it with `activeworksheet.Refresh` but that didn't work, it says an object is required. – elguapo Jul 25 '17 at 18:35
  • 1
    Add `BackgroundQuery:=False` to your `qt.Refresh` and your code should wait until the query is finished refreshing. – Tim Williams Jul 25 '17 at 18:36
  • you could try `qt.Refresh False` one of the other questions on this topic suggests that is a possible solution but less ideal than the event-handler. – David Zemens Jul 25 '17 at 18:37
  • Hey, so I tried `BackgroundQuery = False` after my `qt.Refresh` but it didn't seem to do the trick – elguapo Jul 25 '17 at 19:45
  • I should also mention that I am using Excel 2011 on Mac, so some of the options that I would have on Microsoft are not available to me. – elguapo Jul 25 '17 at 19:46
  • You should mention that in the body of the question itself (don't expect everyone to read all of the chatty comments). Also, you should specify which of the "options" are not available to you since not everyone is familiar with Excel for Mac. – David Zemens Jul 25 '17 at 20:21

1 Answers1

0

What I understand that code will wait for sometime and then run if it is correct you can use Application.Wait(Now + TimeValue("0:00:05")). This will wait for 5 second and then execute next line.

Asif Tailor
  • 1
  • 5
  • I've tried this, I'm not too sure why but it doesn't work and slows the program down a lot – elguapo Jul 25 '17 at 17:30
  • What do you mean by query table loads, sorry but I didn't get u explain it with example so that u can get better answer. – Asif Tailor Jul 25 '17 at 17:34
  • Hey so I basically I have a query table that pulls data from Google Finance. After running the `qt.Refresh` command, it takes a while for Excel to pull all the data. About 10 to 15 seconds. Because of this, my for loop runs before the data is pulled, making it useless. I tried your fix, but for some reason it won't work, and all it does is slow down the program more. Sorry if I wasn't clear, did this help clear things up more? – elguapo Jul 25 '17 at 17:40
  • 1
    Try this...Do Loop While ActiveSheet.QueryTables("yourqueryname").Refreshing – Asif Tailor Jul 25 '17 at 17:50
  • I'm sorry, I'm new to VBA coding and am not sure how exactly to incorporate that into my code. I added the whole code into my post, hopefully that helps make things a bit more clear – elguapo Jul 25 '17 at 18:27
  • @vba.Beginner you would put that `Do` loop right *after* the call to `qt.Refresh`. I haven't tried that, but it might work. – David Zemens Jul 25 '17 at 18:33
  • Hmm, I'll have to figure out how exactly to create a do loop first, sorry I'm new to VBA coding so I'm still grasping the basics. – elguapo Jul 25 '17 at 19:46