Insert formulas by means of a Macro

  • Thread starter Thread starter Mucah!t
  • Start date Start date
M

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
 
'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
 
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
 
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
Click to expand...

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.
 
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

= = = =
 
Back
Top