need to automate date of last data entry

[lneilson]

Hi

this is the code that gets my to the sample I attached:

'Get the last used row within the collection of used columns.
lngRowLast = Cells(Rows.Count, "A").End(xlUp).Row
'Get the last used column.
intColLast = Cells(lngRowLast, Columns.Count).End(xlToLeft).Column
'adjust to remove footer rows
lngRowLast = lngRowLast - 2

Cells(1, intColLast + 1).Resize(lngRowLast).FormulaR1C1 = "=SUM(RC1:RC" & intColLast & ")"
Cells(lngRowLast + 1, intColLast + 2).Resize(, 7).Value = Array("Sun.", "Mon.", "Tue.", "Wed.", "Thu.", "Fri.", "Sat.", "Sun.")
Cells(1, intColLast + 2).Resize(lngRowLast, 7).FormulaR1C1 = _
"=SUMIF(R" & lngRowLast + 1 & "C1:R" & lngRowLast + 1 & "C" & intColLast & ",R" & _
lngRowLast + 1 & "C," & "RC1:RC" & intColLast & ")"
Sheets("Sheet3").Select

I would to automate in column after saturday the date of each rows data
entry from row 1 to row 10 and the next column I would like to the number of days it has been since entered

thanks for any help

Edited by Aussiebear: Louie, when posting code to the forum, can you please use the vba button to encapsulate the code.... it makes it so much easier to read?

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim LastCol As Long

On Error GoTo ws_exit
Application.EnableEvents = False

LastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
LastCol = Me.Cells(LastRow - 1, Me.Columns.Count).End(xlToLeft).Column

If Target.Column < LastCol - 7 Then

Me.Cells(Target.Row, LastCol + 1).Value = Date
Me.Cells(Target.Row, LastCol + 1).NumberFormat = "dd mmm yyyy"
Me.Cells(Target.Row, LastCol + 2).FormulaR1C1 = "=TODAY()-RC[-1]"
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[lneilson]

thanks for the reply

pasted the code where you said but nothing happened how do I run it

thanks

lneilson

[Bob Phillips]

Make a change within A1:HS11 and watch it.

[lneilson]

thanks for the reply
sorry it took so long to get back to you

your idea won't work for me I am trying to automate my program so all I have to do is push my start button all I do is create and print charts then
delete the workbook I'm all most there just need this formula to run in the 1st column to the right of saturday
=LOOKUP(2,1/(A1:gl1<>""),$A$13:$h$13)
and this in the next column
=DATEDIF(IF1,NOW(),"d")
I still have to edit these everyday do you have any ideas on how to
to incorporate this in to existing code?

thanks again

lneilson