Cell content recognition and modification if required.

[TMan293]

I have a quarterly report template with columns for each month followed by a quarterly total with a year end total as the last column. The monthly headers include "Month-Year" "mmm-yy" the quarterly column headers contain the quarter and year "1st Quarter 2022" and the year end column header is "YYYY Total". I am trying to create vba syntax that will update all column headers to the current year when opened for the 1st quarter compilation. I am able to update the individual month's year and the year end column's year, but not the quarterlies. A sample of the column headers are below and I am currently working with this syntax:
If WSM1.Cells(1, VVV) = ("1st Quarter" & YrL) Then
WSM1.Cells(1, VVV) = ("1st Quarter" & Yr)

This should evaluate the 1st quarter header as "1st Quarter & YrL", when I hover over the "WSM1.Cells(1, VVV)" it shows "1st Quarter 2022" bit it is not recognizing it as ("1st Quarter" & YrL), thus it is not updating to the current year.

Jan-22

Feb-22

Mar-22

1st Quarter 2022

 

[Bosquedeguate]

I have a quarterly report template with columns for each month followed by a quarterly total with a year end total as the last column. The monthly headers include "Month-Year" "mmm-yy" the quarterly column headers contain the quarter and year "1st Quarter 2022" and the year end column header is "YYYY Total". I am trying to create vba syntax that will update all column headers to the current year when opened for the 1st quarter compilation. I am able to update the individual month's year and the year end column's year, but not the quarterlies. A sample of the column headers are below and I am currently working with this syntax:
If WSM1.Cells(1, VVV) = ("1st Quarter" & YrL) Then
WSM1.Cells(1, VVV) = ("1st Quarter" & Yr)

This should evaluate the 1st quarter header as "1st Quarter & YrL", when I hover over the "WSM1.Cells(1, VVV)" it shows "1st Quarter 2022" bit it is not recognizing it as ("1st Quarter" & YrL), thus it is not updating to the current year.

​ Jan-22

Feb-22

Mar-22

1st Quarter 2022

Are YrL and Yr named ranges? What is in the cells (ranges)?
Or are YrL and Yr variables in your code? What are their values?
also what is the value of VVV and is it a named range or VBA varialbe? Where does it's value come from?

Please provide more of your code.
You appear to be using VBA to create you headers? I that correct and is it necessary.

 

[Bosquedeguate]

I have a quarterly report template with columns for each month followed by a quarterly total with a year end total as the last column. The monthly headers include "Month-Year" "mmm-yy" the quarterly column headers contain the quarter and year "1st Quarter 2022" and the year end column header is "YYYY Total". I am trying to create vba syntax that will update all column headers to the current year when opened for the 1st quarter compilation. I am able to update the individual month's year and the year end column's year, but not the quarterlies. A sample of the column headers are below and I am currently working with this syntax:
If WSM1.Cells(1, VVV) = ("1st Quarter" & YrL) Then
WSM1.Cells(1, VVV) = ("1st Quarter" & Yr)

This should evaluate the 1st quarter header as "1st Quarter & YrL", when I hover over the "WSM1.Cells(1, VVV)" it shows "1st Quarter 2022" bit it is not recognizing it as ("1st Quarter" & YrL), thus it is not updating to the current year.

​ Jan-22

Feb-22

Mar-22

1st Quarter 2022

You are missing a space " " betweeen "Quater" and YrL. Your string being tested is:
1st Quarter2022, the string in Cell(1, VVV) is
1st Quarter 2022

You have the same problem (missing a space in the string that you build with Yr
Its should be:
"1st Quarter " & Yr (note space added after Quarter)

Here is my test code that works with the "1st Quarter 2022" text in Cell (2,1) or Range(A2).
I created a simple function that creates the Header for a the specific quarter and year.

Option Explicit
Public Sub CreateHeaders()
  Dim WSM1 As Worksheet
  Dim YrL, Yr
  Dim VVV
  Set WSM1 = Worksheets("Sheet1")
  
  VVV = 1
  YrL = 2022 'Year Last
  Yr = 2023 'Current Year
  If WSM1.Cells(2, VVV) = QuarterHeader(1, YrL) Then
      WSM1.Cells(2, VVV) = QuarterHeader(1, Yr)
  End If
End Sub

Private Function QuarterHeader(qtr, year) As String
  If qtr > 0 And qtr < 5 Then
    QuarterHeader = Choose(qtr, "1st", "2nd", "3rd", "4th") & " Quarter " & year
  Else
    QuarterHeader = ""
  End If
End Function