Automatically advance cell reference in message box each year

[Ironman]

Hi and a Happy New Year to you!

I have this message box code

MsgBox "Monthly Tracking VBA: Change cell from AN1 to AO1 for " & Year(Now()), vbInformation, "New Year Update 2 of 2"

I'd be grateful for some code that will automatically advance the cell references each year, so Jan 1 2023 shows

"Monthly Tracking VBA: Change cell from AO1 to AP1 for " & Year(Now()), vbInformation, "New Year Update 2 of 2"

and so on at the start of each year.

Many thanks!

 

[Michael M]

Are these references based on a last column for that row ??

AO1 to AP1

 

[NoSparks]

You're already using Dante's code to set f to the cell of the the current year.
so the address for the current year in that message would be f.address(0,0) and last year would be f.offset(,-1).address(0,0)

 

[NoSparks]

too late to edit previous post:
and next year would be f.offset(,1).address(0,0)

 

[Ironman]

Hi Michael & Nolan, Happy New Year to you both!

@Michael M - the last visible column, which is unhidden on Jan 1 every year.
@NoSparks - many thanks, although this is a different sheet that doesn't use Dante's code.

 

[Michael M]

@Ironman Do you mean like this

MsgBox "Monthly Tracking VBA: " & Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column) & " " & Year(Now()), vbInformation, "New Year Update 2 of 2"

 

[Ironman]

@Ironman Do you mean like this

MsgBox "Monthly Tracking VBA: " & Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column) & " " & Year(Now()), vbInformation, "New Year Update 2 of 2"

Ah, I hadn't thought of it that way - this is a Workbook_Open event on Jan 1. What other info do you need so it will locate the next column AO1 (and AP1 etc) in the Monthly Tracking sheet to fit the message box "Monthly Tracking VBA: Change cell from AN1 to AO1 for " & Year(Now()), vbInformation, "New Year Update 2 of 2"?

 

[Michael M]

MAybe this way then...

Dim lc As Integer
With Sheets("Monthly Tracking")
MsgBox "Monthly Tracking VBA: " & .Cells(1, .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column).Value & " " & Year(Now()), vbInformation, "New Year Update 2 of 2"
End With

 

[Ironman]

Thanks Michael - I only got a message box with "Monthly Tracking VBA: ". You're way more clued up than I am but isn't the "*" a wildcard rather than a defined starting point such as AN1?

 

[Michael M]

Sorry, I assumed that row 1 would hold the last column !
BUT, if you have data beyond the desired column in row 1 it will return that value !
Try using

Dim lc As Integer
With Sheets("Monthly Tracking")
MsgBox "Monthly Tracking VBA: " & .Cells(1, .Cells(1, Columns.Count).End(xlToLeft).Column).Value & " " & Year(Now()), vbInformation, "New Year Update 2 of 2"
End With