Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace - Limit search to a specific column | Excel Worksheet Functions | |||
Search Specific Column for a matched entry | Excel Programming | |||
search column for specific cell using vba | Excel Programming | |||
search a column for a specific piece of data using vba | Excel Programming | |||
search column for a specific piece of data using vba | Excel Programming |