VB Code If..End If Statement

[backfromthebush]

I think this is probably an easy solution but I can't figure it out!

I have the following IF statement

    '...Return to Data sheet and move down one cell
    Sheets("DATA").Select
    ActiveCell.Offset(1).Select
 
    '...exits procedure if new active cell is blank
    If ActiveCell.Value = Null Then Exit Sub
    End If

What then is meant to happen is that I repeat the rest of my procedure until i hit a blank cell, which will then exit.

However I keep getting "Compile Error: End If Without Block If"

Can anyone help?!

 

[jeffreybrown]

With this line

If ActiveCell.Value = Null Then Exit Sub

You have in effect ended the If statement so no need for the

End If

 

[Blade Hunter]

Is that the complete code?

If not can you post the rest? You may have a bung reference elsewhere.

 

[backfromthebush]

righto, it seemed to work, but here is my main issue...

What I am trying to achieve is to extract data automatically from tables in a particular website (www.worldstadiums.com). I have a list of all the URL's of the tables, and I have them in a sheet called "DATA" in column A. Column B then has the name of the country for that particular URL (e.g. Algeria, Angola, etc.)
I want the procedure to pick up the cell with the URL and put it automatically into the procedure, and then extract the table from the website. It will then repeat this process for each of the URLs.
Ive pasted my code below. It works when the URL is written into the code (at ActiveSheet.QueryTables.Add(Connection:= "URL:http:// etc etc.), but when I try and add in the web address as a string it throws out a Runtime Error 1004 Cannot Connect To The Internet.... any suggestions?

Sub mcrExtractData()
    '...Insert new sheet & rename & get web address
    Dim strSheetName As String
    Dim strWebAddress As String
    Dim NewWorksheet As Worksheet
    Sheets("DATA").Select
    [COLOR=magenta]strWebAddress[/COLOR] = ActiveCell.Value
    ActiveCell.Offset(, 1).Select
    strSheetName = ActiveCell.Value
    Set NewWorksheet = Sheets.Add(After:=Sheets(Sheets.Count))
    NewWorksheet.Name = strSheetName
 
    '...Run Data Extract
        Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        [COLOR=magenta]strWebAddress[/COLOR], Destination _
        :=Range("$A$1"))
        .Name = strSheetName
        .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 = "17"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    '...Return to Data sheet and move down one cell
    Sheets("DATA").Select
    ActiveCell.Offset(1).Select
 
    '...exits procedure if new active cell is blank
    If ActiveCell.Value = "" Then Exit Sub
    'End If
 
   '...repeats procedure
   ActiveCell.Offset(, -1).Select
   Call mcrExtractData
 
End Sub

Examples from column A & B of DATA sheet is:
<TABLE style="WIDTH: 435pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=579 border=0><COLGROUP><COL style="WIDTH: 355pt; mso-width-source: userset; mso-width-alt: 17298" width=473><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 355pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=473 height=20>URL; http://www.worldstadiums.com/africa/countries/algeria.shtml</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=106>Algeria</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>URL; http://www.worldstadiums.com/africa/countries/angola.shtml</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Angola</TD></TR></TBODY></TABLE>