Visual Basic Help
Forum rules
We once roamed the vast forums of Corona Coming Attractions. Some of us had been around from The Before Times, in the Days of Excelsior, while others of us had only recently begun our trek. When our home became filled with much evil, including the villainous Cannot-Post-in-This-Browser and the dreaded Cannot-Log-In, we flounced away most huffily to this new home away from home. We follow the flag of Jubboiter and talk about movies, life, the universe, and everything, often in a most vulgar fashion. All are welcome here, so long as they do not take offense to our particular idiom.
We once roamed the vast forums of Corona Coming Attractions. Some of us had been around from The Before Times, in the Days of Excelsior, while others of us had only recently begun our trek. When our home became filled with much evil, including the villainous Cannot-Post-in-This-Browser and the dreaded Cannot-Log-In, we flounced away most huffily to this new home away from home. We follow the flag of Jubboiter and talk about movies, life, the universe, and everything, often in a most vulgar fashion. All are welcome here, so long as they do not take offense to our particular idiom.
-
Mal Shot First
- Wall of Text Climber - 2500 Posts
- Posts: 2733
- Joined: January 10th, 2014, 5:05 pm
-
Mal Shot First
- Wall of Text Climber - 2500 Posts
- Posts: 2733
- Joined: January 10th, 2014, 5:05 pm
Re: Visual Basic Help
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