Adding a loop to a recorded macro

[bcconnor]

Hello,

I have been searching this forum and found some similar posts in regards to adding a loop to a recorded macro and I can't seem to modify my macro to make it work. I apologize for posting such a similar topic.

I have a spreadsheet with about 300 records consisting of Lat1 Long1 Lat2 Long2 as my columns starting at C21:F21 and then C22:F22 and so on. I copy and paste the first row into an existing formula at J8:M8 and it calculates the azimuth and distance. which is in P50 and J42. I copy and paste the values into G21 and H21. Then I move to the next row and repeat the process. I recorded a macro for this but I can't seem to make this into loop for it to loop through all of the records. Can anyone help me with this?

Thanks in advanced!

 

[earthworm]

share excel and macro

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub bcconnor()
   Dim Cl As Range
   
   For Each Cl In Range("C21", Range("C" & Rows.Count).End(xlUp))
      Range("J8:M8").Value = Cl.Resize(, 4).Value
      Cl.Offset(, 4).Value = Range("P50").Value
      Cl.Offset(, 5).Value = Range("J42").Value
   Next Cl
End Sub