Macro to cycle through classes and add a date format to a specific cell

[Hyflex]

Hi,

I am wanting to create a macro to easily fix some documents that I receive every week, there are way way too many classes for me to go through and fix the date manually so I am looking for a bit of help!

The document looks pretty similar to what's attached, I've just removed all of the private information.

All I need is a looping macro to cycle through each class and change the date (green box) to have the "Small Date" formatting, bare in mind it must be the UK date layout: 25/01/2019 (dd/mm/yyyy)

1. The first class always appears on row 16.
2. The classes are never contain the same amount of people, for example the first one only contains 4 but the next one contains 12...
3. The Unique TItles are the same for each class (I've surrounded a class in a thick border), for example I've given an example just below "Date" which is "Average SPCD"
4. Column A is the best column to use for working out the length of each class because the other columns could potentially have missing data

I've also added a sheet called "WantedAppearance" to show what I am trying to achieve, it should make it very very clear that way, just in case I have explained poorly.

If you could add some comments to the macro it would be useful because that way I can figure the code out

I'd appreciate the help Thanks

[jolivanes]

Please Login or Register  to view this content.

[Hyflex]

Sub Maybe()
Dim heute As String, c As Range
heute = Format(Date, "dd/mm/yyyy")
For Each c In Range("L1:L" & Cells(Rows.Count, 12).End(xlUp).Row)
If c.Value = "Date" Then c.Offset(, 1) = heute
Next c
End Sub

The big problem with that is it's checking every single row is it not? The sheet is huge and it takes quite a long time.

I managed to figure out one solution:

Please Login or Register  to view this content.

Can it be done better?

[jolivanes]

Re"it takes quite a long time"
Yes, looping is not normally the way to go in a huge file. I was not aware of the size of the file.
How long does it take to run?
Autofilter might be a better way.

[jindon]

See if this is faster,

Please Login or Register  to view this content.

[jolivanes]

Or try like so.

Please Login or Register  to view this content.