Replace functions with hard values

[joshua1990]

Good morning guys!

I have a big data sheet with a lot of LOOKUP-Functions.
A very trivial idea is now to replace the LOOKUP-functions for all data before the last week.
The tbl have this simple structure:

Date - Week - Functions - Functions - Functions - Functions

So, the second column defines the current date/ week.
The objective is that at opening the file a code will run and check the current date/ week and will replace the columns C to F with the hard values/ fix values until the current week.


Is this with VBA possible?


Best regards
JoshuaFixValues.xlsx

[mana]

Option Explicit


Sub test()
    Dim r As Range
    
    With Columns("B")
        Set r = .Find(What:=WorksheetFunction.WeekNum(Date), _
                        LookIn:=xlValues, LookAt:=xlWhole, _
                        After:=.Cells(1), SearchDirection:=xlPrevious)
    End With
    
    If r Is Nothing Then Exit Sub
'
    With Range("C2:F" & r.Row)
        .Value = .Value
    End With

End Sub

マナ