vba - Call a function in a loop

Question

I need to call an homemade function (GetCoordinates) in a loop and apply it at each loop step.

I have a column with data (Starting at L2) and I need to apply my personnal function GetCoordinates to this data and write the result on the column next to it (so starting at M2)

NumRows = Range("L2", Range("L2").End(xlDown)).Rows.Count
      Range("L2").Select
      For x = 1 To NumRows
         Selection.Offset(, 1).Formula = "=GetCoordinates(L&x)"
         ActiveCell.Offset(1, 0).Select
      Next

My problem is that I don't know how to apply my function to each cell. How should it be written so that the function is called for each cell in the loop? (The GetCoordinates function works fine)

score 1 · Accepted Answer · 2018-07-17 09:26:01Z

1

Try putting in all the formulas without a loop. To write te formulas in column M and use column L as the formulas argument then use,

 Range(cells(2, "M"), cells(rows.count, "L").End(xlup).offset(0, 1)).Formula = _
     "=GetCoordinates(L2)"

Your own might have been closer with,

Selection.Offset(, 1).Formula = "=GetCoordinates(L" & x & ")"

But you were mismatching Do ... While and For ... Next loops.

edited Jul 17, 2018 at 9:26

answered Jul 17, 2018 at 9:15

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

What column is the input data and what column do you want the formula in?

Unck Over a year ago

The input is the L column and the result should be in the M column (both on the same line)

Santosh · Accepted Answer · 2018-07-17 09:21:46Z

1

Try this

 NumRows = Range("L" & Rows.Count).End(xlUp).Row

  For x = 2 To NumRows
     Cells(x, "M").Value = GetCoordinates(Cells(x, "L"))
  Next

answered Jul 17, 2018 at 9:21

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

2 Comments

Unck Over a year ago

Thank you it worked as well. Yet I don't know which one of the 2 solutions is the fastest (i have about 6000 lines to process)

Santosh Over a year ago

Add a timer at start of code Debug.Print "Start Time: " & Time and at the end Debug.Print "Start Time: " & Time , you will come to know. :-)

Collectives™ on Stack Overflow

vba - Call a function in a loop

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related