IF Statement

[Jonas]

I can't seem to get my code to work. My Excel program uses MS Mappoint
to get latitude and longitude. I only want the latitude and longitude
when the results quality is "geoFirstResultGood" otherwise I want the
cell to be skipped. I thought that if I put the word "then" after the
line "If objApp.ActiveMap.FindAddressResults(straddress, City,
state).ResultsQuality = geoFirstResultGood," and the word "else" at the
bottom of the program, like this:

".....ActiveCell.Offset(intCounterT, 6).Value = dblLon

intCounterT = intCounterT + 1

Loop

Else

intCounterT = intCounterT + 1

Loop

That didn't work. Below is block of code that I have been working
with. Hopefully you will be able to tell me what I am not
understanding; I'm stumped.

"Sub TheprogramGetAddressandLatitude()

Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objLoc As MapPoint.Location
Dim strDir As String * 1
Dim dblLat As Double

'Set up the application

Set objMap = objApp.ActiveMap
objApp.Visible = True
objApp.UserControl = True


Range("B1").Activate

intCounterT = 1

Do While ActiveCell.Offset(intCounterT, 0) <> ""

straddress = ActiveCell.Offset(intCounterT, 1)
City = ActiveCell.Offset(intCounterT, 2)
state = ActiveCell.Offset(intCounterT, 3)

'Test if good address
If objApp.ActiveMap.FindAddressResults(straddress, City,
state).ResultsQuality = geoFirstResultGood Then MsgBox "this is a good
address."

'Find an address
Set objLoc = objMap.FindAddressResults(straddress, City, state)

'Get latitude of this location
dblLat = objLoc.Latitude

'Get longitude of this location
dblLon = objLoc.Longitude

'If the latitude is positive, set direction to North
If dblLat > 0 Then
strDir = "N"
'If the latitude is negative, set direction to South
ElseIf dblLat < 0 Then
strDir = "S"
'Set blank if the latitude is zero
Else
strDir = ""
End If

'Display the latitude with degree symbol and N/S
MsgBox "Latitude: " + Format(Abs(dblLat), "0.00000") + Chr(176) +
strDir

ActiveCell.Offset(intCounterT, 5).Value = dblLat

MsgBox "longitude " & dblLon

ActiveCell.Offset(intCounterT, 6).Value = dblLon



intCounterT = intCounterT + 1


Loop


End Sub"

Your response is appreciated.

 

[Tom Ogilvy]

Sub TheprogramGetAddressandLatitude()

Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objLoc As MapPoint.Location
Dim strDir As String * 1
Dim dblLat As Double

'Set up the application

Set objMap = objApp.ActiveMap
objApp.Visible = True
objApp.UserControl = True


Range("B1").Activate

intCounterT = 1

Do While ActiveCell.Offset(intCounterT, 0) <> ""

straddress = ActiveCell.Offset(intCounterT, 1)
City = ActiveCell.Offset(intCounterT, 2)
state = ActiveCell.Offset(intCounterT, 3)

'Test if good address
If objApp.ActiveMap.FindAddressResults( _
straddress, City,state).ResultsQuality = _
geoFirstResultGood Then

MsgBox "this is a good address."

'Find an address
Set objLoc = objMap.FindAddressResults(straddress, City, state)

'Get latitude of this location
dblLat = objLoc.Latitude

'Get longitude of this location
dblLon = objLoc.Longitude

'If the latitude is positive, set direction to North
If dblLat > 0 Then
strDir = "N"
'If the latitude is negative, set direction to South
ElseIf dblLat < 0 Then
strDir = "S"
'Set blank if the latitude is zero
Else
strDir = ""
End If

'Display the latitude with degree symbol and N/S
MsgBox "Latitude: " + _
Format(Abs(dblLat), "0.00000") + Chr(176) + strDir

ActiveCell.Offset(intCounterT, 5).Value = dblLat

MsgBox "longitude " & dblLon

ActiveCell.Offset(intCounterT, 6).Value = dblLon






End if

intCounterT = intCounterT + 1
Loop


End Sub"

 

[Jonas]

Tom,

Thanks for responding. I tried out the code that you posted but it
didn't work. I'm getting a type 13 mismatch. When I try to debug the
program, the following line is highlighted: "
Set objLoc = objMap.FindAddressResults(straddress, City, state)" I
can't figure out why this is the case. Are there restrictions on the
number of lines that can be in an IF statement? Let me know if you
have anymore ideas.

 

[Tom Ogilvy]

Jonas,
that is certainly a humorous response.
I didn't write that line of code - I just redesigned your logic a little.
That may be the first time your code has ever gotten to that line - so now
you need to debug your own code.

You define objLoc as
Dim objLoc As MapPoint.Location

Your type mismatch error would indicate that
objMap.FindAddressResults(straddress, City, state)

doesn't return a MapPoint.Location object.

I would put in a line above it

msgbox typename(objMap.FindAddressResults(straddress, City, state))
and see what it is returning or spend some time in the object model for
mappoint.

 

[Jonas]

Tom,

I know that you didn't write the code but you helped out by cleaning it
up a bit. I added back a little bit of code that I had before I
originally posted the code that was pertaining to Mappoint and it
worked fine. Your suggestions were helpful. Many thanks.

 

[rub]

did you put in Dim intCounterT as integer
and shouldn't ActiveCell.Offset(intCounterT, 0) be
ActiveCell.Offset(intCounterT, 0).value

 

[Tom Ogilvy]

I don't have mappoint installed, so I don't have access to the object model,
but I do understand error messages.

http://support.microsoft.com/kb/302885/en-us

seems to indicate that FindAddressResults returns a FindResults collection
(a collection of location objects), but not a location object as you have
dimmed your object reference.

http://msdn.microsoft.com/library/d...-us/MapPoint2006/BIZOMMFindAddressResults.asp

confirms that is the case.

A quote from another page:
"Use the FindAddressResults method on the Map object to return a collection
of Location objects (a FindResults collection) that are possible address
find matches."

Perhaps you have rearranged your code so this line isn't executed again, but
if it is actually code that you need, you may want to take another look.

Good luck.