Search specific column for a variable date

[J Smith 555]

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

 

[Joel]

Try removing the After:=ActiveCell

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

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

 

[Joel]

I can't tell from your code where the active cell was located. there may be
a problem if the activecell was not in the range of rToSearch

 

[Rick Rothstein]

Okay, you got the answer to your question, but I would like to mention a
couple of things about your code if you don't mind.

The only thing I can see the above line doing is it removes the time portion
from the Now function's return value. VB has a Date function that returns
this value immediately. So, the above line could be written this way...

UseDate = Date

You should only use parentheses when required by syntax. Since you are not
returning a value from the MsgBox function, it is being used like a
subroutine... subroutines only require parentheses when the Call keyword is
used with it; otherwise they are not required. While using parentheses
doesn't hurt anything in this particular case, there are times when using
unnecessary parentheses with a subroutine call that is not using the Call
keyword will produce incorrect results or an error... it is better to get
into the habit of using parentheses only when necessary.

You used the above construction several times in your code. Since UseDate
was declared as a Date variable, the CDate function call here is just an
unnecessary time waster.

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

Now, ignoring the various MsgBox calls above which I assume are in there for
debugging purposes, all of the above code can be replaced by this single
statement...

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

Note that this single code line eliminates the requirement for the UseDate
variable, so you can remove its Dim statement as well.

 

[J Smith 555]

Rick,

Thank you for all of your tips, it has helped out a lot. I was originally
using CDate(UseDate) to force my data to be stored as a 'date' vs a 'string'
(which turned out to be not needed.) :-D

I have added your suggestion to replace my Select Case statement but I'm
having a bit of trouble understanding how the math works out for the below
string

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

** This is my understanding:
B/c my case statements subtract a minimum of 2 days (from whatever day it
happens to be) the first part makes sense. I get a little lost when the
Weekday(Date, vbMonday) < 3) is brought in. From what I can gather it resets
the 'Weekday' start to Monday (which now makes the default value of vbsunday
= 7 vs 1. )
Now b/c of that the two days where I subtract 4 instead of 2 fall inside the
new defined range of weekday(date, vbMonday) 2 & 1 (Tuesday & Monday
respectfully) the formula would breakdown today as:

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = Date (11/4/08) - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - 2)
CheckForDate = 10/31/08 *** [I think]

Jason

 

[Rick Rothstein]

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

** This is my understanding:
B/c my case statements subtract a minimum of 2 days (from whatever day it
happens to be) the first part makes sense. I get a little lost when the
Weekday(Date, vbMonday) < 3) is brought in. From what I can gather it
resets
the 'Weekday' start to Monday (which now makes the default value of
vbsunday
= 7 vs 1. )
Now b/c of that the two days where I subtract 4 instead of 2 fall inside
the
new defined range of weekday(date, vbMonday) 2 & 1 (Tuesday & Monday
respectfully) the formula would breakdown today as:

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = Date (11/4/08) - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - 2)
CheckForDate = 10/31/08 *** [I think]

Your post has made my day... it is always my hope when I post some code that
the OP will actually try to decipher why it works as opposed to simply
copying it blindly.

It might be easier to understand how the code line works by expanding
expression (that is, multiply the 2 across the parentheses). If we do that,
then this...

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

becomes this...

CheckForDate = Date - 2 + 2 * (Weekday(Date, vbMonday) < 3))

The Date - 2 part you understand. As for the rest, let me first remind you
that logical expressions in VB evaluate to -1 when True. So, when the
weekday (with the week starting on Monday) evaluates to 1 or 2 (which as you
figured out occurs on Monday and Tuesday), the -1 that the logical
expression returns is multiplied by 2 and then added to the part you already
understand (remember, you are adding an negative value, so in reality it is
equivalent to a subtraction). The net result is that 2 days are subtracted
on all day except Monday and Tuesday when an additional 2 days are
subtracted as well.

 

[J Smith 555]

Rick,

Thank you so much for taking the time and breaking down how the formula
works. I have now added it to my script and I am going to go back and modify
previous sections of my code with your suggestions. :-D

- Jason

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

** This is my understanding:
B/c my case statements subtract a minimum of 2 days (from whatever day it
happens to be) the first part makes sense. I get a little lost when the
Weekday(Date, vbMonday) < 3) is brought in. From what I can gather it
resets
the 'Weekday' start to Monday (which now makes the default value of
vbsunday
= 7 vs 1. )
Now b/c of that the two days where I subtract 4 instead of 2 fall inside
the
new defined range of weekday(date, vbMonday) 2 & 1 (Tuesday & Monday
respectfully) the formula would breakdown today as:

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = Date (11/4/08) - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - 2)
CheckForDate = 10/31/08 *** [I think]

Your post has made my day... it is always my hope when I post some code that
the OP will actually try to decipher why it works as opposed to simply
copying it blindly.

It might be easier to understand how the code line works by expanding
expression (that is, multiply the 2 across the parentheses). If we do that,
then this...

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

becomes this...

CheckForDate = Date - 2 + 2 * (Weekday(Date, vbMonday) < 3))

The Date - 2 part you understand. As for the rest, let me first remind you
that logical expressions in VB evaluate to -1 when True. So, when the
weekday (with the week starting on Monday) evaluates to 1 or 2 (which as you
figured out occurs on Monday and Tuesday), the -1 that the logical
expression returns is multiplied by 2 and then added to the part you already
understand (remember, you are adding an negative value, so in reality it is
equivalent to a subtraction). The net result is that 2 days are subtracted
on all day except Monday and Tuesday when an additional 2 days are
subtracted as well.