VBA: IF Date <= TODAY() replace formula with Value

[Ubiquitous]

Hi, I've had a look on the forums for the above and I have found this: VBA: IF Date <= TODAY() replace formula with Value

I have tried to adapt it to my needs but I get errors in the VBA. My knowledge of VBA is very limited as only just starting to learn it.

I have a large Spreadsheet which shows staff members working times and duties performed. The date in question is in ROW 8 and each date is in ascending columns (4 years worth so about 1400 columns) starting from Column P.

I get some of the data from a download that cannot be back dated. So when the new download is run, if the date in ROW 8 of my spreadsheet is before the date range in my download (or TODAY for the sake of this issue) it returns #N/A as there is no data to find.

What I would like to happen is to change the data in ROWS 10-470 to a VALUE/TEXT if the Date for that Column in ROW 8 is before today. I tried this but I think an additional piece of code is required to look at ROW 8 and then set contents in ROWS 10-470 to value

Sub MakeValuesIfLessThanOrEqualToToday()
Dim R As Long
Dim CalcState As Long
Dim ScreenUpdateState As Boolean
Dim StatusBarState As Boolean
Dim EventsState As Boolean

' Save the current state of Excel settings.
ScreenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
CalcState = Application.Calculation
EventsState = Application.EnableEvents

' Turn off Excel functionality to improve performance.
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For R = 8 To 470
If Cells(R, "P").Value <= Date Then Cells(R, "Q").Resize(, 13).Value = Cells(R, "Q").Resize(, 13).Value
Next
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub


Any help with this would be much appreciated

 

[maabadi]

Welcome to MrExcel Message Board.
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.