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

Ubiquitous

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

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,228
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top