VBA: Search through column for 2 partial matches

[Zanmato]

Hi all,

I'm sure some of you are looking at the thread title thinking "this sounds easy". Well, I don't think it is. Hope I'm wrong though.

I am almost done writing* a long module to automate the formatting of a report which is pulled from a web-based database. We (at work) have to run this report and manually format it for every member of staff in our organisation once a month, so if I can crack this it will save us a lot of time.

The report generates into Excel. The first column is the week number in this format: "Week ## [Month] [Year] (##)" - the number in brackets at the end is variable based on the number of entries in one of the latter columns. Shouldn't be relevant to this query.
The Week number this week is 18, and it's this week (and weeks like it) that are causing my final problem because it contains days from 2 months (Monday and Tuesday were April, Weds-Fri are May) so the report has 2 seperate entries for this week: "Week 18 April 2019 (##)" and "Week 18 May 2019 (##)". Part of the code I've just written is to add a "Totals" row beneath each week. Currently I have this which works, but it adds one beneath both the aforementioned entries, rather than just beneath Week 18 May as we require.

Dim r As Range

For Each r In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
            If r.Value Like "Week *" Then
                r.Offset(1, 0).EntireRow.Insert Shift:=xlDown
            End If
Next r

The reason I've written* the code this way, well 2 reasons: 1) I am really new to this and 2) Column A contains merged cells. I read somewhere that VBA really hates merged cells, so rather than trying to use "for each cell in column A that contains a value" I went with "for each cell in column A that has this value". Dunno if it would have worked that way or not. Probably. Anyway, enough excuses for my possibly poor coding.

I cannot find on google, nor figure out a way to even begin writing a piece of code that will say this:

For Each r In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
            If r.Value Like "Week *" Then
                r.Offset(1, 0).EntireRow.Insert Shift:=xlDown
                   ' UNLESS the 6th and 7th characters of r.Value are the same as the 6th and 7th characters of the next r.Value
            End If
Next r

Now, I realise that's probably not the way the solution (if there is one) would be worded, but hopefully you understand what I mean.



Any flashes of genius would be hugely welcomed.

Thanks for reading!

*Disclaimer: When I refer to writing code, I really mean finding what I need on google and painstakingly adapting it to suit my own needs. Please don't assume I am anything other than a dunce when it comes to this stuff.