Insert formulas by means of a Macro
 

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

Insert formulas by means of a Macro
 

'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


"Mucah!t" wrote:

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

Insert formulas by means of a Macro
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

'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


"Mucah!t" wrote:

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

Insert formulas by means of a Macro
 

On 14 apr, 13:46, "Mucah!t" wrote:
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.

Insert formulas by means of a Macro
 

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

= = = =

Wouter HM wrote:
On 14 apr, 13:46, "Mucah!t" wrote:
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.