Trouble getting IE to stop when there is no data in the column

[dangerdavedsp]

So I made this Macro to open up IE and enter in the data for columns a and b, then extract info from the website. The problem I am having is that since I set "a" as an integer, it will just keep going through row 500 or so. What I really need it to do is stop when there are no more addresses. Can someone help me with this? Attached is the spreadsheet with the code in it.

Thanks!GeoTax Query.xlsm

Also here is the code

Sub GeoTax()


Dim a As Integer


For a = 1 To 500



Set ie = CreateObject("internetexplorer.application")
ie.Visible = False
ie.Navigate "http://www.geotax.com/USTaxLookup/"

Do While ie.ReadyState <> 4 Or ie.Busy = True
DoEvents
Loop



If ie.document.Title = "GeoTAX - Free Instant Sales and Use Tax Rates" Then
ie.document.getelementbyid("tbAddress").Value = Range("Address")(a + 1)
ie.document.getelementbyid("tbLastline").Value = Range("Zip")(a + 1)
ie.document.all("tbTaxValue").Value = "100"
ie.document.getelementbyid("btnGO").Click
Do While ie.ReadyState <> 4 Or ie.Busy = True

DoEvents
Loop

If Left(ie.document.getelementbyid("__VIEWSTATE").Value, 15) <> "/wEPDwUIOTEyOTE" Then



Range("Muni")(a + 1).Value = ie.document.getelementbyid("lblmuni").innerText
Range("County")(a + 1).Value = ie.document.getelementbyid("lblcounty").innerText
Range("State")(a + 1).Value = ie.document.getelementbyid("lblState").innerText
Range("County")(a + 1).Value = ie.document.getelementbyid("lblcounty").innerText
Range("State")(a + 1).Value = ie.document.getelementbyid("lblState").innerText
Range("SalesTax")(a + 1).Value = ie.document.getelementbyid("lblSalesTax").innerText
Range("UseTax")(a + 1).Value = ie.document.getelementbyid("lblUseTax").innerText

End If

End If


' ie.Quit



Next a



End Sub

[ragulduy]

either change your for loop to a do until loop or insert an if ... exit for command.

e.g.

sub macro_1()
dim count
count = 1
do until Range("A" & count) = ""
  'do code
loop
end sub

or

sub macro_2()
dim count
for count = 1 to 500
  if Range("A" & count) = "" then exit for
  'do code
end if

[dangerdavedsp]

Sorry, I am just learning VBA. Would you mind giving me the complete code of the two options you provided?


Thanks!!

[ragulduy]

Sub GeoTax()
Dim a As Integer
For a = 1 To 500
    If Range("Address")(a + 1) = "" Then Exit For
    Set ie = CreateObject("internetexplorer.application")
    ie.Visible = False
    ie.Navigate "http://www.geotax.com/USTaxLookup/"
    Do While ie.ReadyState <> 4 Or ie.Busy = True
        DoEvents
    Loop
    If ie.document.Title = "GeoTAX - Free Instant Sales and Use Tax Rates" Then
        ie.document.getelementbyid("tbAddress").Value = Range("Address")(a + 1)
        ie.document.getelementbyid("tbLastline").Value = Range("Zip")(a + 1)
        ie.document.all("tbTaxValue").Value = "100"
        ie.document.getelementbyid("btnGO").Click
        Do While ie.ReadyState <> 4 Or ie.Busy = True
            DoEvents
        Loop
        If Left(ie.document.getelementbyid("__VIEWSTATE").Value, 15) <> "/wEPDwUIOTEyOTE" Then
            Range("Muni")(a + 1).Value = ie.document.getelementbyid("lblmuni").innerText
            Range("County")(a + 1).Value = ie.document.getelementbyid("lblcounty").innerText
            Range("State")(a + 1).Value = ie.document.getelementbyid("lblState").innerText
            Range("County")(a + 1).Value = ie.document.getelementbyid("lblcounty").innerText
            Range("State")(a + 1).Value = ie.document.getelementbyid("lblState").innerText
            Range("SalesTax")(a + 1).Value = ie.document.getelementbyid("lblSalesTax").innerText
            Range("UseTax")(a + 1).Value = ie.document.getelementbyid("lblUseTax").innerText
        End If
    End If
    ' ie.Quit
Next a
End Sub

[dangerdavedsp]

Thanks so much!!!

[dangerdavedsp]

Anyway you could update the macro you made for me a long time ago for the new website. I have no idea why it isn't working. Here is the new website

http://taxco.g1.com/