JW:

I was just cleaning out my mailbox and I found this reponse. I never
realized you responded.

Is there any way you could still help me out with this? I have been doing
this manually over the last month.

I played around with the code you provide and can not seem to get it
working. I have placed a state abbreviation in Column a in the worksheet and
then all the corresponding counties in columns B through S but it is not
working? For example - I am trying to do Texas. I typed "tx" in A1 and have
the counties listed out in B1 to N1. The next line - row 2 has some more info
to lookup --- A2 = "tx" and B2 to J2 there are more counties. And so on, and
so on.

What am I doing wrong here? I will definitely keep my eyes on your replies
this time. If there is anyway I can provide you a sample workbook with what I
am referring to, let me know.

Thanks,
Chris

"JW" wrote:

On Sep 24, 5:18 pm, IntricateFool
wrote:
You are awesome! Now if I can just have it loop through a range then i would
be set. any suggestions?

I really would like to learn how to do this, just don't know where to start...

Many Thanks!

Chris


So, are the counties listed on the rows from B - S? Where are the
states listed? Are those in column A? If so, this should work.
This will cycle through all of the cells in column A (containing the
states) and all of the cells in the applicable row (containing the
counties) and place the return in the first available row in column A,
which will of course place the data in A:D. If this isn't what you
are after, please let me know. Be sure to test this to make sure it
is what you want before running it on your main data.
Sub this()
Dim cty As String, state As String
Dim BotRow As Long
Dim i As Long, j As Integer
For i = 2 To Cells(65536, 1).End(xlUp).Row
For j = 2 To Cells(i, 1).End(xlToRight).Column
state = Cells(i, 1).Text
cty = Cells(i, j).Text
BotRow = Cells(65536, 1).End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://getzips.com/CGI-BIN/ziplook.exe?
What=3&County=" & _
cty & "&State=" & state & "&Submit=Look+It+Up", _
Destination:=Range("A" & BotRow))
.Name = cty & " " & state
.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 = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next j
Next i
End Sub