CODEWITHSUNDEEP
excelvba
Jimmy
Jimmy

Reputation: 12517

Use VBA to paste a formula if there is something in column A of that row

I'm trying to run some VBA that will count how many rows there are which are not empty in a given range, and then paste a formula in column 13 (M) the number of rows down which were not empty.

This is the code I have:

Sub CountCells()
MsgBox WorksheetFunction.CountA(Sheets("DATA").Range("A7:A750"))
Worksheets("DATA").Range("M7:M500").Formula = "=MYFORMULAR"
End Sub

This code currently counts the number of cells which are not empty in column A but then how do I take this number and use it for the next equation?

If there were 200 columns in range A7:A750 with content in, I would like to paste my formular from M7 to M207.

Upvotes: 0

Views: 42

Answers (1)

urdearboy
urdearboy

Reputation: 14590

Option Explicit

Sub CountCells()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("DATA")
Dim LRow As Long

'Determine last row
LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

'Apply formula from rows 7 to last row
ws.Range("M7:M" & LRow).Formula = "=MYFORULAR"

End Sub

Upvotes: 1

Related Questions