Replace Cell with Value

[Neight]

Hello,
I am new to Excel and this Forum. I am a student and currently utilizing a User Defined Function I found code for that geocode's addresses utilizing the Google maps api. Below is the code for the function I am using. I was hoping someone could help me have this function replace the cell's contents with it's value at the end the function executing. I have tried various methods I have seen while scouring countless forums and have gotten nothing but errors.

=IF(E1<>"",IF(E1="INT",mygeocode(F1&" "&G1),mygeocode(E1&" "&F1&" "&G1)),"")

I am using this formula down the whole column to determine if there is address data and if so, execute the function, otherwise, leave the cell blank. Unfortunately, I have too many addresses and my spreadsheet crashes when it tries to geocode every time it opens. Also, Google Maps API has usage restrictions as well. So, if someone knows how to have this function replace the cell with the value at the end of each use, I should be able to resolve these issues. Any help would be greatly appreciated.

Function MyGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
'Assemble the query string
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"
'define XML and HTTP components
Dim googleResult As New MSXML2.DOMDocument
Dim googleService As New MSXML2.XMLHTTP
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
'create HTTP request to query URL - make sure to have
'that last "False" there for synchronous operation
googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName("geometry")
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
MyGeocode = strLatitude & "," & strLongitude

Application.Wait (Now + TimeValue("0:00:01"))


Next oNode
Else
MyGeocode = "Not Found (try again, you may have done too many too fast)"
End If

End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function

 

[HackSlash]

A. Test it out! You tell me. (I think that ".value" should be blank and ".formula" would show you the equation.)
B. No, a subroutine runs when you tell it to run.
C. No, I mean the line that says ".value = .value" we are "sealing" the value and discarding the formula

This article about macros has an example of how to make a macro run on workbook open at the end:

https://support.office.com/en-us/article/run-a-macro-5e855fd2-02d1-45f5-90a3-50e645fe3155

 

[Neight]

I got it to work. I did not like the Dim YourColumn as char so I changed that to String. Then I found the drop down in the macro menu that allowed it to execute when changing worksheets. So I ended up with this code and it work like a charm!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Dim YourColumn As String
YourColumn = "M"


LastRow = Worksheets("Sheet1").Range(YourColumn & Rows.Count).End(xlUp).Row


For i = 1 To LastRow
If Worksheets("Sheet1").Range(YourColumn & i).Value <> "" Then
      
   Worksheets("Sheet1").Range(YourColumn & i).Value = Worksheets("Sheet1").Range(YourColumn & i).Value
 
End If
Next i
End Sub

Thank you so much for the guidance and patience.