Loop to copy formula

Question

I have an formula that looks up the value in Column A against another system. I have this copy/paste macro that is working fine, however I have to change the values in order for it to work.

CopyPaste Macro
'   Dim I
    For i = 1 To 15
    ActiveCell.FormulaR1C1 = "=MMSIDLookupItemCount(RC[-1])"
    ActiveCell.Offset(1).Select
    Next i

This puts the formula in column B. What I would really like to do is loop through until there is a blank cell in column A and I have tried many different things and I can't get it to work.

Would anyone be able to help? I am really trying to learn my VBA but my skills are very basic!

see: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba for finding the last row with a value. — Scott Craner, Jun 02 '21 at 15:20

score 0 · Answer 1 · answered Jun 02 '21 at 16:08

Try this. First it gets the column letter for the column to the left of the current cell (so if you select cell B1, ColumnLetter would be A). Then it finds the last row in that column. And finally it uses that as the limit in the for loop.

Dim ColumnLetter As String
ColumnLetter = Split(Cells(1, ActiveCell.Column - 1).Address, "$")(1)

Dim LastRow As Long
LastRow = ActiveSheet.Range(ColumnLetter & ActiveSheet.Rows.Count).End(xlUp).Row

For i = 1 To LastRow
ActiveCell.FormulaR1C1 = "=MMSIDLookupItemCount(RC[-1])"
ActiveCell.Offset(1).Select
Next i

Loop to copy formula

1 Answers1