CODEWITHSUNDEEP
excelvba
Ibrahim
Ibrahim

Reputation: 175

Using macro in a for loop

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

Upvotes: 1

Views: 54

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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)

Upvotes: 2

Related Questions