Solved: .Find won't work!!!

[lynnnow]

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:

[vba]
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

[/vba]

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

[lynnnow]

This is really funny, the code started working now, without any intervention.

What could this bug be?

[Aussiebear]

Could it be that the file didn't load correctly?

[lynnnow]

Hi Aussiebear,

Tried that route too, but didn't help then, though it worked afterward.

[Dr.K]

Pretty sure I can explain this, I just got burned by this yesterday.

From the Excel VBA help file:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Ok, this is a little unclear at first. What its saying is that those four settings are saved statically IN THE GUI (Excel application), NOT IN THE PROJECT FILE!
Further more, these settings are saved ANY time Find is used, including when Find is used from the GUI! (from the Edit menu, or Ctrl-F)

This means that if you don't explicitly call these settings each time, you are at the mercy of whatever the last usage was, wether it was the GUI or VBA code. If you are using the Ctrl-F to try and debug your .Find problems, you could be making a recursive mess. Try it out:

[vba]
Sub Test1()
expression.Find(What, , xlValues, xlWhole, xlByRows)
End Sub

Sub Test1()
expression.Find(What, , xlValues, xlPart, xlByRows)
End Sub
[/vba]

Run the subs, and then go into the GUI Find box and check to see that the options have changed. Frankly, at first I didn't believe my own eyes, since this seems to be the only VBA Method that acts like this.


Here is my solution:

The Help says to specify ALL options EVERY time, but this is over kill: if the first .Find in your code specifies the options, and all of the following uses of .Find use the same options, then you don't have to specify every time. Also, specifiying every time can make your code span multiple lines, and therefore be a little harder to read.

So, if ALL of the .Find uses in a proceedure use the same options, I only specifiy it once, during the first usage. If I have multiple .Finds that need different options, then I specify them every time, becuase its too hard to keep track of.

NOTE: Most of the lookup Ranges I use are 1-dimensional (all cells in one row OR one Column). This means that I don't need specifiy SearchOrder, becuase it doesn't make a bit of difference for 1-D Range.


EDIT: Oh yeah, you are having problems with dates. Remember how Excel stores dates: depending on what your data looks like, you might be able to tweak the finding behavior by switching the LookIn option between xlFormulas and xlValues.

[lynnnow]

Dr. K you solved my problem. Thanks a bunch for explaining that out to me. Yes, I had not used the LookIn:= part in the find. The reason, I guess, it worked afterward is because I've got another macro that has to search for a value in another worksheet and display data from that worksheet. Also, my first .Find in this macro is to search for a text value while the other is a date value which is stored as m/d/yyyy but displayed as dd-mmm. Gotta use the full .Find function now onwards. Thanks again.

Lincoln