Ubiquitous
New Member
- Joined
- Jan 20, 2021
- Messages
- 3
- Office Version
- 2016
- Platform
- Windows
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
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