Hi Gurus,
I have a piece of code that moves values from one sheet to another. Part of a code is to set X & Y (dates and products). Y works just fine. X only works if the dates on a target sheet are hard coded, but once I change them to a TODAY()+1 style formula, code does not find X. Does anybody have a clue where the code needs amending to identify date formula?
Set R = Range("AF35:ED35")
For Each cell In R
If cell.Value = vbNullString Then
Else
With Sheets(2)
x = cell.Offset(-22).Value
y = Sheets(1).Range("Y14").Value
Set rfound = .Cells.Find(What:=DateValue(x), After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If rfound Is Nothing Then
Else
mycol = rfound.Column
Set cfound = Sheets(2).Cells.Find(What:=y, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If cfound Is Nothing Then
Else
myrow = cfound.Row
.Cells(myrow, mycol).Value = cell
End If
End If
End With
End If
cell.ClearContents
Next cell
A million thanks in advance!
Bookmarks