VBA: IF year < TODAY() replace formula with Value

[G MJ]

Hi,

I've been trying to figure out a VBA that will remove formulas and replace with the Values calculated if the year is less than current year.

I need the formulas in B8:B13 to change into values if the year in B4 is less than the date in B1. This will also need to be done in the other rows if their year is less than current.

Any Help will be appreciated.

	2021
	1	2	3	4	5	6
Total Cost	2020	2021	2022	2023	2024	2024
£ 150,000.00	£2,000	£2,000	£2,000	£2,000	£2,000	£2,000
£ 17,250.00	£690	£690	£690	£690	£690	£690
£ 3,000.00	£120	£120	£120	£120	£120	£120
£ 9,000.00	£360	£360	£360	£360	£360	£360
£ 9,000.00	£360	£360	£360	£360	£360	£360
£ 9,000.00	£360	£360	£360	£360	£360	£360

 

[Joe4]

Try this:

Sub MyMacro()

    Dim lc As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Find last column in row 4 with data
    lc = Cells(4, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns, starting with column 2 (B)
    For c = 2 To lc
'       Check to see if year in row 4 is less than current year
        If Cells(4, c) < Year(Date) Then
'           Change rows 8:13 to values
            Range(Cells(8, c), Cells(13, c)).Value = Range(Cells(8, c), Cells(13, c)).Value
        End If
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

 

[G MJ]

That works perfectly, thank you very much.

 

[Joe4]

You are welcome.
And welcome to the Board!