lynnnow
01-15-2008, 09:29 PM
Hi,
I've got an attendance muster and I've got to send the previous day's attendance to the tech team for the number of users for that day. I wrote code for it which functioned marvelously yesterday, but refuses to work today. I've made a userform with a combobox holding the dates for the month, the dates are entered as d/d/yyyy format but displayed as d-mmm format. This is the code I've got:
Private Sub CommandButton1_Click()
If ComboBox1.Text = "" Then
MsgBox "No input, quitting procedure...", vbCritical
Unload Me
Exit Sub
Else
With Range("A5:A34")
Set DayShift = .Find("1.00 P.M.")
If Not DayShift Is Nothing Then
CellAddress = Mid(DayShift.Address(False, False, xlA1), 2, Len(DayShift.Address(False, False, xlA1)))
End If
End With
With Range("C4:AG4")
Set FoundDate = .Find(ComboBox1.Value) 'This is not found
If Not FoundDate Is Nothing Then
FoundDate.Activate
DateRange = FoundDate.Address(False, False, xlA1)
Else
MsgBox "Date not found...", vbCritical, "...::: Lynx's Corer :::..."
Unload Me
Exit Sub
End If
End With
PresentCount = 0
Range(DateRange).Offset(1, 0).Activate
Do While ActiveCell.Address(False, False, xlA1) <> Range(DateRange).Offset(CellAddress - 3, 0).Address(False, False, xlA1)
If ActiveCell.Value = "P" Then
If ActiveCell.Comment Is Nothing Then
PresentCount = PresentCount + 1
End If
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
NewAddress = ActiveCell.Offset(1, 0).Activate
PresentCount2 = 0
SecondPart = ActiveCell.Offset(15, 0).Address(False, False, xlA1)
Do While ActiveCell.Address(False, False, xlA1) <> SecondPart
If ActiveCell.Value = "P" Then
If ActiveCell.Comment Is Nothing Then
PresentCount2 = PresentCount2 + 1
End If
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
Label1.Caption = "Day : " & PresentCount & vbCr & _
"Night : " & PresentCount2 & vbCr & _
"Total : " & PresentCount + PresentCount2
End If
End Sub
Private Sub UserForm_Initialize()
Dim Dates(31)
Range("C4").Activate
i = 0
Do While Not IsEmpty(ActiveCell.Value)
Dates(i) = Format(ActiveCell.Value, "d-mmm")
ActiveCell.Offset(0, 1).Activate
i = i + 1
Loop
ComboBox1.List() = Dates
CommandButton1.Caption = "Search Database"
CommandButton2.Caption = "Create mail and close form"
Frame1.Caption = "Attendance Mailer Generator"
UserForm1.Caption = "...::: Lynx's Corner :::..."
Range("A1").Activate
Label1.Caption = ""
Label1.Font.Size = 14
Label1.Font.Bold = True
End Sub
I've attached a snapshot of how the dates look. Please help. I just fail to see why the .Find won't work today when it worked yesterday, after trying all possible combinations for debugging it.
Lincoln
I've got an attendance muster and I've got to send the previous day's attendance to the tech team for the number of users for that day. I wrote code for it which functioned marvelously yesterday, but refuses to work today. I've made a userform with a combobox holding the dates for the month, the dates are entered as d/d/yyyy format but displayed as d-mmm format. This is the code I've got:
Private Sub CommandButton1_Click()
If ComboBox1.Text = "" Then
MsgBox "No input, quitting procedure...", vbCritical
Unload Me
Exit Sub
Else
With Range("A5:A34")
Set DayShift = .Find("1.00 P.M.")
If Not DayShift Is Nothing Then
CellAddress = Mid(DayShift.Address(False, False, xlA1), 2, Len(DayShift.Address(False, False, xlA1)))
End If
End With
With Range("C4:AG4")
Set FoundDate = .Find(ComboBox1.Value) 'This is not found
If Not FoundDate Is Nothing Then
FoundDate.Activate
DateRange = FoundDate.Address(False, False, xlA1)
Else
MsgBox "Date not found...", vbCritical, "...::: Lynx's Corer :::..."
Unload Me
Exit Sub
End If
End With
PresentCount = 0
Range(DateRange).Offset(1, 0).Activate
Do While ActiveCell.Address(False, False, xlA1) <> Range(DateRange).Offset(CellAddress - 3, 0).Address(False, False, xlA1)
If ActiveCell.Value = "P" Then
If ActiveCell.Comment Is Nothing Then
PresentCount = PresentCount + 1
End If
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
NewAddress = ActiveCell.Offset(1, 0).Activate
PresentCount2 = 0
SecondPart = ActiveCell.Offset(15, 0).Address(False, False, xlA1)
Do While ActiveCell.Address(False, False, xlA1) <> SecondPart
If ActiveCell.Value = "P" Then
If ActiveCell.Comment Is Nothing Then
PresentCount2 = PresentCount2 + 1
End If
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
Label1.Caption = "Day : " & PresentCount & vbCr & _
"Night : " & PresentCount2 & vbCr & _
"Total : " & PresentCount + PresentCount2
End If
End Sub
Private Sub UserForm_Initialize()
Dim Dates(31)
Range("C4").Activate
i = 0
Do While Not IsEmpty(ActiveCell.Value)
Dates(i) = Format(ActiveCell.Value, "d-mmm")
ActiveCell.Offset(0, 1).Activate
i = i + 1
Loop
ComboBox1.List() = Dates
CommandButton1.Caption = "Search Database"
CommandButton2.Caption = "Create mail and close form"
Frame1.Caption = "Attendance Mailer Generator"
UserForm1.Caption = "...::: Lynx's Corner :::..."
Range("A1").Activate
Label1.Caption = ""
Label1.Font.Size = 14
Label1.Font.Bold = True
End Sub
I've attached a snapshot of how the dates look. Please help. I just fail to see why the .Find won't work today when it worked yesterday, after trying all possible combinations for debugging it.
Lincoln