I ran this code on the sample sheet you provided. I typed in June 20, 2015 into a couple of the cells in the A column and ran the code.
The VLookups in the columns to the right of the A cells that I typed the date into were replaced with their values. The #N/A cells were ignored.
Option Explicit
Sub ReplaceVLookups()
Dim wsMay2015 As Worksheet, lr As Long, c As Long
Dim cellDate As Long, dtToday As Long
dtToday = Date
Set wsMay2015 = Worksheets("May 2015")
lr = wsMay2015.Cells(Rows.Count, "A").End(xlUp).Row
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
With wsMay2015
For c = 4 To lr
If Not IsDate(.Cells(c, 1).Value) Then GoTo 1
cellDate = .Cells(c, 1).Value
If cellDate + 1 = Date Then
.Range(.Cells(c, 1), .Cells(c, 7)).Value = .Range(.Cells(c, 1), .Cells(c, 7)).Value
End If
1 Next c
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Bookmarks