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]

Let's try something simple. Put this after your cell is set:

Range(Application.Caller.Address).Value = Range(Application.Caller.Address).Value

 

[HackSlash]

Nevermind, I tested all sorts of this and it doesn't work as expected. The problem is that the function sets the value of the cell when it completes so any attempt to modify the cell inside the function is null. You will have to seal the cell value from outside the function, after the function has run. You need a cleanup macro that does maintenance on your sheet before you save it.

 

[Neight]

I really appreciate the effort. I suppose there is no way to get the range from which the function was used then copy, pastespecial.value for that range after it executes is there? And if not, any idea how to write the macro in a way that prevents my formulas that are waiting for address data from being wiped out?

 

[HackSlash]

I tried using:
Range(Application.Caller.Address).Copy

but like I said this would have to be done outside the function, after it's done.

So if you had a macro to seal the work already done it would just look at the values with the formulas. You already have them set to "" so it should be fairly straight forward.

If YourRange.Value <> "" Then
[INDENT]YourRange.Value = YourRange.Value[/INDENT]
End If

This says any cell value that is not blank is equal to it's value (Not the formula)

You have to define "YourRange" because I don't know what cells you are looking at on your sheet. You might need a FOR...NEXT loop to walk through each cell in your workbook.

 

[Neight]

Bear with me, I'm new at this ...

Would that look something like?

Dim i As Integer
Dim YourRange As Range
Set YourRange = Range(M:M)

For i=1 To Rows.Count
If YourRange.Value <> "" Then
YourRange.Value = YourRange.Value
End If
Next i

Or does VBA not recognize an entire column the same way as excel does?

Also, is there any way to have excel execute this macro when there is a change to a cell in that range or anytime there is anything but a blank?

 

[HackSlash]

That's good! I would attempt to look at only used cells with the last row formula. Then you need to include "i" in the range evaluation.

Try this:

Dim i As Integer
Dim YourColumn As Char
YourColumn = "M"

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

For i=1 To LastRow
[INDENT]If Range(YourColumn & i).Value <> "" Then
[/INDENT]
      [INDENT]   Range(YourColumn & i).Value = Range(YourColumn & i).Value
[/INDENT]
 [INDENT]End If[/INDENT]
 Next i

 

[HackSlash]

and here is the code for a sub that runs when a worksheet changes. You could use this to seal the value of "target.address" but I do advise against this.

<code>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("M:M")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

        ' Place your code here.
        Range(<code>Target.Address</code>).Value = Range(<code>Target.Address</code>).Value
       
    End If
End Sub</code>

all this code is untested and will need debugging.

 

[Neight]

Ok, I think I understand most of what you did there. I can't thank you enough for your time an patience. I just have a couple questions as I am trying to learn as I go. I hope you don't mind.

A. For the last row formula when it's checking the value to be greater or less than blank, the formula I have filled down the entire column won't cause a false trigger?
B. Will the first segment of code execute only when the workbook opens or closes?
C. When you mentioned sealing Target.Value, do you mean entering a specific cell or range?

 

[Neight]

Also, if I decide not to use the code for when a cell changes, will the code just execute on open or close or does that require a separate line of code?
I assume if I have a multiple page workbook I need to define a page I want the cleanup macro to execute on as well.