HELP with inserting formula
 

Hi Everyone,

I would like a Macro that Inserts the Following Formula into all of my Excel
Sheet in the Cell "A4" Please.
The formula would be: A4 = COUNTA($B$7:last active row).
I want to mention that the last row varies.
I looked at the other discussion, but I didnt quite understand€¦
Thank you in advance,

Dragos

--
Dragos

HELP with inserting formula
 

give this a try

Option Explicit
Dim lastrow As Long
Dim ws As Worksheet
Sub addformula()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For Each ws In Worksheets
ws.Range("A4").Formula = "=counta($B$7:B" & lastrow & ")"
Next ws
End Sub

--


Gary


"Dragos" wrote in message
...
Hi Everyone,

I would like a Macro that Inserts the Following Formula into all of my
Excel
Sheet in the Cell "A4" Please.
The formula would be: A4 = COUNTA($B$7:last active row).
I want to mention that the last row varies.
I looked at the other discussion, but I didn't quite understand.
Thank you in advance,

Dragos

--
Dragos

HELP with inserting formula
 

Sub AddFormula()
Dim sh As Worksheet
Dim iLastRow As Long
Dim iLastCol As Long

For Each sh In ActiveWorkbook.Worksheets
iLastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
If iLastRow < 7 Then iLastRow = 7
iLastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
If iLastCol < 2 Then iLastCol = 2
sh.Range("A4").FormulaR1C1 = "=COUNTA(R7C2:R" & iLastRow & "C" &
iLastCol & ")"
Next sh

End Sub



--
HTH

Bob Phillips

"Dragos" wrote in message
...
Hi Everyone,

I would like a Macro that Inserts the Following Formula into all of my
Excel
Sheet in the Cell "A4" Please.
The formula would be: A4 = COUNTA($B$7:last active row).
I want to mention that the last row varies.
I looked at the other discussion, but I didn't quite understand.
Thank you in advance,

Dragos

--
Dragos