1

I'm searching for a way to use a function (get the coordinates from an address. Here is the code that I am using https://myengineeringworld.net/2014/06/geocoding-using-vba-google-api.html) in a for-loop since I have more than 5000 rows. Is it possible to use the cells(row, col).Value = function. If yes, how can I?

Sub forLoop()
    Dim rw as Integer
    for rw = 680 to 700
        Cells(rw,7). Value = getCoordinates(rw,5) 'in the column 5 I have the adress
    next i
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ibrahim
  • 163
  • 1
  • 11
  • 2
    Welcome to SO. Whenever you use a variable to hold the row or column index you should declare it as long and not integer. – Stavros Jon Jul 17 '19 at 08:48
  • Integers get cast to a Long internally anyway - so there's not really any point in using them unless you specifically want to throw a run-time error when you exceed the upper boundary of an integer.... – SierraOscar Jul 17 '19 at 09:03

1 Answers1

2

You probably meant to use 

Sub forLoop()
    Dim rw As Long 'should be long!
    For rw = 680 to 700
        Cells(rw, 7).Value = getCoordinates(Cells(rw, 5).Value) 
    Next rw 'must be rw not i
End Sub

Note that if you have the code of the getCoordinates function in a module, you should also be able to use it as a formula instead of VBA.

Just write the following formula into column G:

=getCoordinates(E:E)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Do we have a canonical answer about using `Long` that we can link to? I feel like I have to explain it all the time... – SierraOscar Jul 17 '19 at 09:05
  • 1
    @sam yes we have: [Why Use Integer Instead of Long?](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) – Pᴇʜ Jul 17 '19 at 09:09