Sub stuff_Lookup() 'clear previous data Sheets("Scrubber").Select Range("I14:S100").Select Application.CutCopyMode = False Selection.ClearContents Range("A9").Select 'Begin the query Set dest = Range("I14") For Each cell In [G14:G64] cell.Select Range("A9").Value = cell.Value 'Check for end of WTN list If Range("A9") = "" Then GoTo Line1 Else With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.stuff.com/support/api/stuff.cgi?item=[""item""]", Destination:=dest) .RefreshStyle = xlOverwriteCells .WebFormatting = xlWebFormattingNone .AdjustColumnWidth = False .PreserveFormatting = True With ActiveSheet.QueryTables(1).Parameters(1) .SetParam xlRange, Range("A9") End With .BackgroundQuery = True .Refresh End With Line1: Range("A1").Select Set dest = dest.Offset(1, 0) Next cell End Sub
Thats the code I ended up using and it works great. I used the For/Next loop to make it run the query a certain number of times. Then I set the "destination" for the query as a variable that is moved down 1 cell each loop. This makes my query results go down by 1 cell from the original one I defined. (using the offset command) I generate the query itself in the code rather than using a query made in excel wizards also. Otherwise this wouldnt work at all.
