PDA

View Full Version : Replace functions with hard values



joshua1990
01-03-2018, 12:17 AM
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
Joshua21281

mana
01-03-2018, 12:57 AM
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



マナ