Hello Everyone,

I have some code that I have been using but seems to only work sporadically.
I keep receiving an error msg even if there is a value inside the column I am
searching in (see below for breakdown of procedure).

Run-Time error '13':
Type mismatch.

When I click on debug it highlights:
Set MyRange = rToSearch.Find(etcetc)

Procedure Summary:
It grabs 'Today's' date (Ie 11/04/08 €“ Tuesday(Case 3)) then assigns it to a
variable .. from there based on the day the script is run (Mon, Tue, etc) it
will subtract a specific amount of days (using same date example, 11/4/08
(Tuesday - 4 days becomes 10/31/08 (Friday)). From there it will search only
the cells inside column 'N' (cells 1,14) reference and look to see if at
least 1 cell inside that row becomes 'True' if not it will subtract 1 from
the value and try again until it becomes 'True'.

** Code below ***

Function FindDateInsideColumn()

Dim UseDate As Date
Dim CheckForDate As Date
Dim MyRange As Range
Dim rToSearch As Range
Dim LoopControl As Boolean

UseDate = CDate(Format(Now, "mm/dd/yy"))
MsgBox (UseDate)
Select Case Weekday(UseDate)
Case 1 ' Sunday
MsgBox ("1")
CheckForDate = CDate(UseDate) - 2 ' Now Friday
Case 2 ' Monday
MsgBox ("2")
CheckForDate = CDate(UseDate) - 4 ' Now Thursday
Case 3 ' Tuesday
MsgBox ("3")
CheckForDate = CDate(UseDate) - 4 ' Now Friday
Case 4 ' Wednesday
MsgBox ("4")
CheckForDate = CDate(UseDate) - 2 ' Now Monday
Case 5 ' Thursday
MsgBox ("5")
CheckForDate = CDate(UseDate) - 2 ' Now Tuesday
Case 6 ' Friday
MsgBox ("6")
CheckForDate = CDate(UseDate) - 2 ' Now Wednesday
Case 7 ' Saturday
MsgBox ("7")
CheckForDate = CDate(UseDate) - 2 ' Now Thursday
End Select

MsgBox (CheckForDate)

' Set rToSearch = Nothing
'Set MyRange = Nothing
LoopControl = False

Do Until LoopControl = True
' On Error Resume Next

Set rToSearch = Range(Cells(2, 14), Cells(Rows.Count, 14).End(xlUp))
' rToSearch.Select

Set MyRange = rToSearch.Find(What:=CheckForDate, After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=x1Previous)

If Not MyRange Is Nothing Then
MsgBox (CheckForDate & " has 1 row in this report")
' MyRange.Select
LoopControl = True
Else
MsgBox ("Something failed")
MsgBox (CheckForDate)
CheckForDate = CDate(CheckForDate) - 1
MsgBox (CheckForDate)
LoopControl = False
End If

Loop

End Function