Insert formulas by means of a Macro

[Mucah!t]

Hello all,

I'm looking for a code which recognizes the last data entered in a
sheet and adds 3 rows of formulas beneath it.

Thanks in advance

 

[Jacob Skaria]

'Recognize the last row with data in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'example Formula in Row 13 to sum a1 to a11
Range("A" & lngLastRow).Formula = "=SUM(A1:B" & lngLastRow-1 & ")"

If this post helps click Yes

 

[Jacob Skaria]

Sorry forgot to increment to the next row..

'Recognize the last row with data in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'example Formula in Row 13 to sum a1 to a11
Range("A" & lngLastRow+1).Formula = "=SUM(A1:A" & lngLastRow & ")"

If this post helps click Yes

 

[Wouter HM]

Hello all,

I'm looking for a code which recognizes the last data entered in a
sheet and adds 3 rows of formulas beneath it.

Thanks in advance

Hi Mucah!t

In Excel 2007 I created this short code sample:

Private Sub Worksheet_Change(ByVal Target As Range)
Static blnActief As Boolean
If Not blnActief Then
blnActief = True
Target.Offset(1, 0).Formula = "=1+" & Target.Address
Target.Offset(2, 0).Formula = "=1+2*" & Target.Address
Target.Offset(3, 0).Formula = "=1+3*" & Target.Address
blnActief = False
End If
End Sub

You need the Static boolean to prevent looping.

HTH,

Wouter.

 

[Dana DeLouis]

You need the Static boolean to prevent looping.

Hi. Just to mention another option. One can prevent the event from
firing while running code via something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Offset(1, 0).Formula = "=1+" & Target.Address
Target.Offset(2, 0).Formula = "=1+2*" & Target.Address
Target.Offset(3, 0).Formula = "=1+3*" & Target.Address
Application.EnableEvents = True
End Sub

HTH
Dana DeLouis

= = = =