Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert formulas in the last row of different worksheets | Excel Programming | |||
Macro to insert formulas on last row of different worksheets | Excel Programming | |||
Macro that will Insert Formulas in last row of different worksheet | Excel Programming | |||
Macro help. Insert row and copy formulas from row above | Excel Programming | |||
Macro to insert copy and insert formulas only to next blank row | Excel Programming |