DanOfEarth
07-07-2010, 07:33 AM
O.K. Because of my shameless neediness, I have donateth a small tith to VBA Express via Paypal. And larger ones shall come.:beerchug:
And this time, I blatenly just can't think of the "loop" or code, although it's simple.
This below query is working great, thanks to GTO. It finds the persons' last name, goes three cells down, tests to see if there is a "phone number". If not, in place of the phone number there is always text saying "More Info". It then copies the phone number if there is one.
However there are multiple instances of the last name on the page. I just need to check each instance until I get a positive with the red code, then leave the loop. I'm assuming I do this with a Do Until statement somewhere in here, or rather I'm thinking the .find method might have a nuance. This should get me to the finish line. This particular method is odd to Google on.
Dim rngFound As Range, rngToTest As Range
Dim sFirstAddress As String
' We've already imported the data we need onto the Import2 sheet. We need to search it for
' the above LastName we are looking for
With Sheets("Import2").Range("A139:A200")
Set rngFound = .Find( _
What:=LastName, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
' This confirms that we found the LastName successfully
If Not rngFound Is Nothing Then
Set rngToTest = rngFound
' This offsets down three cells and captures the phone number
MyPhoneNumber = rngToTest.Offset(3, 0).Value
If MyPhoneNumber <> "More Info" Then
ActiveCell.Offset(0, 7).Formula = MyPhoneNumber
Else
End If
'Note the address of the first found cell so we know where we started.
sFirstAddress = rngFound.Address
'I have no earthly idea what the next FindNext and loop does....oh well
Set rngFound = .FindNext(After:=rngFound)
Do Until rngFound.Address = sFirstAddress
Set rngToTest = Union(rngToTest, rngFound)
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With
When it's done, anybody who wants this, it's a cool crawler that checks a list of addresses against www.yellowpages.com (http://www.yellowpages.com) and automatically strips their phone number and pastes it. I'll rearrange it for a generic use and supply the entire code. It's just this last hurdle of check checking each instance, not just the first one like it's doing now. There will be multiple people in the household, but only one them has a listed phone. I'm trying to capture just one number and move on.
And this time, I blatenly just can't think of the "loop" or code, although it's simple.
This below query is working great, thanks to GTO. It finds the persons' last name, goes three cells down, tests to see if there is a "phone number". If not, in place of the phone number there is always text saying "More Info". It then copies the phone number if there is one.
However there are multiple instances of the last name on the page. I just need to check each instance until I get a positive with the red code, then leave the loop. I'm assuming I do this with a Do Until statement somewhere in here, or rather I'm thinking the .find method might have a nuance. This should get me to the finish line. This particular method is odd to Google on.
Dim rngFound As Range, rngToTest As Range
Dim sFirstAddress As String
' We've already imported the data we need onto the Import2 sheet. We need to search it for
' the above LastName we are looking for
With Sheets("Import2").Range("A139:A200")
Set rngFound = .Find( _
What:=LastName, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
' This confirms that we found the LastName successfully
If Not rngFound Is Nothing Then
Set rngToTest = rngFound
' This offsets down three cells and captures the phone number
MyPhoneNumber = rngToTest.Offset(3, 0).Value
If MyPhoneNumber <> "More Info" Then
ActiveCell.Offset(0, 7).Formula = MyPhoneNumber
Else
End If
'Note the address of the first found cell so we know where we started.
sFirstAddress = rngFound.Address
'I have no earthly idea what the next FindNext and loop does....oh well
Set rngFound = .FindNext(After:=rngFound)
Do Until rngFound.Address = sFirstAddress
Set rngToTest = Union(rngToTest, rngFound)
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With
When it's done, anybody who wants this, it's a cool crawler that checks a list of addresses against www.yellowpages.com (http://www.yellowpages.com) and automatically strips their phone number and pastes it. I'll rearrange it for a generic use and supply the entire code. It's just this last hurdle of check checking each instance, not just the first one like it's doing now. There will be multiple people in the household, but only one them has a listed phone. I'm trying to capture just one number and move on.