Re: Visual Basic Help
Posted: August 26th, 2020, 10:19 am
Thanks! I'll give it a shot.
I meant to get back to you about this a while back and then forgot. I ended up going a slightly different route in solving this issue. I just added an If-Then statement to tell the macro to stop looking when it gets to a cell in column D that doesn't have any data.Jubbers wrote: ↑August 25th, 2020, 10:36 am You could change the For 2 to 400 to be For 2 to However Many Rows There are.
I have to do this in a lot of macros for work. I typically have a variable I call endRow.
endRow = ThisWorkbook.Sheets("WhatEverMySheetsNameIs").Range("G10000").End(xlUp).Row
The 10000 is just some number greater than whatever you think the max number of rows will likely be.
Then For 2 to endRow
Code: Select all
Sub WebQuery()
'
' WebQuery Macro
'
Dim i As Integer
For i = 2 To 400
'
If ActiveSheet.Range("D" & i).Value = "" Then
MsgBox "Process complete.", vbOKOnly + vbInformation
Exit Sub
End If
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("D" & i).Value, Destination _
:=Range("$G$" & ((-13) + ((i - 1) * 15))))
.Name = "Player Info"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
End Sub