Distance Function Not Returning Correct Figure

I am having issues getting my Macro to return the full number when combined with a Google Distance Matrix.

For Example:

10887 Clyde St., Howell, WA

28047 Fir Avenue, Novi, MI

This should return a mileage of over 1000 but my Macro only returns a 1. This happens with any number over 999.

I have attached my Macro.

The function is =getdistance(originaddress,destinationaddress)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[/CODE]

I am pretty sure the problem lies in the Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = ""text"".*?([0-9]+)":

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

After more than half a day, and even though I know very little about using RegExp for this sort of thing, I will note that, when I tried your function, Google returned a "API key is invalid" message -- because I don't have a Google API. I don't know how many of us have Google API keys that we can use, but, as it stands, only those with a Google API key would really be able to help you debug this. I get the impression that you are not having trouble getting a return value from Google, but that your problem is about extracting a number from that text string. I think it would help us help you if you would share typical result strings that Google returns -- what is the value in objHttp.responsetext. Then we may be able to help you without needing to query Google ourselves.