VBA Help Trying to Last 8 Rows in Column A to todays date

[PatrickW1907]

Hi

I've been using vba for a little while and still a novice. I have a spreadsheet where there are a list of dates in column A the date changes every 8 rows i.e. 1 to 8 = 31/08/2022 9 to 16 = 01/09/2022 . I'm looking at automation and want to code a macro that will find the bottom row with data in column A and change the bottom 8 cell values in column A to today's date. I cannot seem to find any useful info I imagine it will be last row -7 and something with the value property but appreciate any help or sdvice from anyone.

Kind regards

Patrick Wood

 

[My Aswer Is This]

Nothing nearly happens automatically in Excel.
But if you paste this code into your sheet and then double click on any cell in column A the script will do as you asked.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  9/3/2022  3:16:28 PM  EDT
If Target.Column = 1 Then
Cancel = True
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, 1).Resize(8).Value = Date
End If
End Sub

 

[Peter_SSs]

.. change the bottom 8 cell values in column A to today's date. ... I imagine it will be last row -7 and something with the value property

Welcome to the MrExcel board!

Sounds like it could be this?

Sub TodaysDate()
  Range("A" & Rows.Count).End(xlUp).Offset(-7).Resize(8).Value = Date
End Sub

 

[PatrickW1907]

Thanks so much for your help guys ! Works perfectly !

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Peter_SSs]

Thanks so much for your help guys ! Works perfectly !

You're welcome. .. but I'd be interested which one did, since they produce quite different results to each other?

 

[PatrickW1907]

Welcome to the MrExcel board!

Sounds like it could be this?

Sub TodaysDate()
  Range("A" & Rows.Count).End(xlUp).Offset(-7).Resize(8).Value = Date
End Sub

Hi Peter

This was the code I used that worked perfectly

Thank You!

 

[Peter_SSs]

Thanks for the clarification.