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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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) "J Smith 555" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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 "J Smith 555" wrote: Joel, Works like a charm !!! Thank you so much for your quick reply. My question is this: If my rToSearch (for example) is defined as Range(N2:N17234).Find(What:= blahlah) .. why would the After:=ActiveCell trip up the code when I have the SearchDirect:=x1Previous ?? Wouldn't the script then start at N17234 then N17233, etc regardless of where the ActiveCell was ?? Jason "Joel" wrote: 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) "J Smith 555" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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. UseDate = CDate(Format(Now, "mm/dd/yy")) 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 MsgBox (UseDate) 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. CheckForDate = CDate(UseDate) - 2 ' Now Friday 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. -- Rick (MVP - Excel) "J Smith 555" wrote in message ... Joel, Works like a charm !!! Thank you so much for your quick reply. My question is this: If my rToSearch (for example) is defined as Range(N2:N17234).Find(What:= blahlah) .. why would the After:=ActiveCell trip up the code when I have the SearchDirect:=x1Previous ?? Wouldn't the script then start at N17234 then N17233, etc regardless of where the ActiveCell was ?? Jason "Joel" wrote: 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) "J Smith 555" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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" wrote: 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. UseDate = CDate(Format(Now, "mm/dd/yy")) 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 MsgBox (UseDate) 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. CheckForDate = CDate(UseDate) - 2 ' Now Friday 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. -- Rick (MVP - Excel) "J Smith 555" wrote in message ... Joel, Works like a charm !!! Thank you so much for your quick reply. My question is this: If my rToSearch (for example) is defined as Range(N2:N17234).Find(What:= blahlah) .. why would the After:=ActiveCell trip up the code when I have the SearchDirect:=x1Previous ?? Wouldn't the script then start at N17234 then N17233, etc regardless of where the ActiveCell was ?? Jason "Joel" wrote: 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) "J Smith 555" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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. -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search specific column for a variable date
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) |
Reply |
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 |