Dates

[Rick S.]

I could use some help, I have a scenerio that I need to determine when a date
falls near the 2nd day of a month or the last day of the month. I have read
info from Mr. Pearson's site that makes my head hurt but not necessarily
solve my problem.
I have on my worksheet a date entered by a user (sDateDue) that can be
current to many years old and I have a userform (sDate) that a user will
enter a date to compare with.

Basically I need to do this:
'If sDateDue is 1st of month and sDate is 2nd of month = No (Do not populate
"To Do List")
'If sDateDue is 1st of month and sDate is 31st of month = Yes (Populate "To
Do List")
'If sDateDue is 1st of month and sDate is 1st of month = Yes (Populate "To
Do List")

I am lost with out logic!
As always, your help is appreciated.
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Conan Kelly]

Rick S.

Please provide more info:

Is sDateDue is a cell on a worksheet? If so, what cell and what worksheet?
Is "sDateDue" the named range of that cell?

Is sDate is the name of the form? If not, what is the name of the form?
What is the name of the control on the form where the user enters the sDate?

Where do you want your results? ...a message box? ...a cell (what is the
sheet name and the cell)? ...a label (or other control) on the form (what
is the name of the control)?

What triggers this comparison? ...a button on the form (what is the name of
the button)? ...the control that the user entered the sDate in loosing
focus? ...the form loosing focus?

Do you want the form to be hidden or closed?

In the 3 senarios you have listed, are these dates in the same month?
....are they in different months? ...does it matter? ...does one have to be
before/after the other?

Conan

 

[Rick S.]

Easier said than done.
"Part Number" is a worksheet (1)
"To Do List" is a work sheet (2)
"Not Found" is a worksheet (3)
All code below resides in a userform with three text boxes:
1. UserPart
2. WorkOrderNum
3. UserDate
CommandButton1 to execute (OK)

Now that I scared everyone away with this superfragilistic looking code,
regardless if you follow my code, or if you can or want to (LOL), I am hoping
to find an example of how to trap Dates in my original scenerio.

'======'Option Explicit is assumed
Sub GetPartNumber()
Application.ScreenUpdating = False

'======'Start
With Sheets("Part Number")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:A" & Sh1LastRow)
End With

sFound = False
For Each Sh1Cell In Sh1Range
If Sh1Cell.Value Like "*" & sUserPart & "*" Then
sFound = True
Application.Goto Reference:=Worksheets("Part
Number").Range(Sh1Cell.Address), _
Scroll:=True
sRev = Sh1Cell.Offset(0, 1).Value
vSelection = MsgBox("Use this selection? " & Sh1Cell.Value & " "
& "Rev " & sRev, vbYesNo)
If vSelection = vbYes Then
sFound = True
'======02.07.08
sh1x1 = Replace(Sh1Cell.Address, "$", "")
sh1x2 = Replace(sh1x1, "A", "")
sh1x3 = Replace(sh1x1, "A", "E")
'sDateDue = Range(sh1x3).Value 'rem 02.13.08
sRowData = sh1x1 & ":I" & sh1x2
sDateDue = Sheets("Part Number").Range(sh1x3).Value
d1 = sDateDue 'from "Range(sh1x3).Value" worksheet "Part
Number"

'============Begin sheet "To Do List"
With Sheets("To Do List")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = .Range("A1:A" & Sh2LastRow)
End With
Sh2LastRow = Sh2LastRow + 1
sh2x1 = "A" & Sh2LastRow 'set row in column A
sh2x2 = Sh2LastRow 'get row number only
Range(sRowData).Copy Destination:=Sheets("To Do
List").Range(sh2x1)

Sheets("To Do List").Select
sDate = GetSetting(appname:="ADM FAIR Due Date Reminder",
section:="Variables", _
Key:="UserDate") 'user entered date from
form
Range("J" & sh2x2).Value = sDate
d2 = sDate

If d1 > d2 Then
sTimeSpan = TimeDiff(d2, d1)
Else
sTimeSpan = TimeDiff(d1, d2)
End If

'If sDateDue is 1st of month and sDate is 2nd of month = No (Do not populate
"To Do List")
'If sDateDue is 1st of month and sDate is 31st of month = Yes (Populate "To
Do List")
'If sDateDue is 1st of month and sDate is 1st of month = Yes (Populate "To
Do List")

Range("M" & sh2x2).Value = y 'TimeDiff, y years
Dim yy As Integer
Range("L" & sh2x2).Value = M 'TimeDiff, M Months
Range("K" & sh2x2).Value = D 'TimeDiff, D Days
Range("O" & sh2x2).Value = WorkOrderNum
yy = Abs(y)
If sDate <= sDateDue Then
If yy >= "3" Then
Range("N" & sh2x2).Value = "Yes"
Range("N" & sh2x2).Select
msgbox "working 1"
'I do some formatting etc. here, removed for clarity
End With
Selection.FormatConditions(1).StopIfTrue = False
Else
Range("N" & sh2x2).Value = "No" 'remove this 02.08.08
End If
If Range("N" & sh2x2).Value = "No" Then
Range("N" & sh2x2).EntireRow.Delete
End If
Columns("A:O").Activate
Selection.Columns.AutoFit
Range("A3").Select
Sheets("Part Number").Activate
'======02.07.08
ElseIf vSelection = vbNo Then
If sFound = True Then
On Error Resume Next
Else
sFound = False
End If
End If
End If
End If
Next Sh1Cell
If sFound = False Then
MsgBox "No Match Found!"
Unload Me
NotFound.Show
GoTo EndIt2
End If
Application.ScreenUpdating = True
UserPart.SelStart = 0
UserPart.SelLength = Len(UserPart.Text)
UserPart.SetFocus
'end of CheckBox1.Value = False
EndIt2:
End sub
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007