Solved: The .FIND Method ... Arrrrrgh!

[Cyberdude]

I'm not sure I've ever used the FIND method successfully, but the following code is my latest unsuccessful attempt:
[VBA]Sub FindTest()
Dim TgtRng As Range
With Worksheets("Main").Range("E12:E14") '<-(A date in each cell)
Set TgtRng = .Find(Range("E13"), LookIn:=xlValues)
If TgtRng Is Nothing _
Then MsgBox "Not Found" _
Else MsgBox TgtRng.Address
End With
End Sub[/VBA]
What am I missing??

[Killian]

That code finds the value of cell E13 from range E12:E14 and returns its address - it will always return "$E$13", no matter what.
I don't understand what your trying to do..?

[Bob Phillips]

I'm not sure I've ever used the FIND method successfully, but the following code is my latest unsuccessful attempt:
[VBA]Sub FindTest()
Dim TgtRng As Range
With Worksheets("Main").Range("E12:E14") '<-(A date in each cell)
Set TgtRng = .Find(Range("E13"), LookIn:=xlValues)
If TgtRng Is Nothing _
Then MsgBox "Not Found" _
Else MsgBox TgtRng.Address
End With
End Sub[/VBA]
What am I missing??

I know the feeling, I hate FIND, it seems such a flaky implementation.

I thinjk the problem that by comparing a single cell value againsta range of dates, it doesn't match. If you try xlFormulas for the Lookin, I think it will do what you expect.

[Cyberdude]

I'm trying to learn how to use the FIND method. So far I have never been successful. Sorry, but the code I posted never finds $E$13. It may have something to do with the fact that I'm searching for a date. I used "Range(E13)" as the search arg, because I could never get a variable to work, so I figured the code I posted SHOULD work. It doesn't. I've tried adding SearchOrder and SearchDirection in various combinations, but no help. The VBA Help is woefully inadequate at explaining its args. (Moan)

[johnske]

I know the feeling, I hate FIND, it seems such a flaky implementation....

I think everyone hates it, it needs a wordy and awkward construct.

Try this Sid...[vba]Option Explicit
Option Compare Text '< ignore case

Sub MSFindItAgain()
Dim Target As Range, FirstAddress As String, It As String
It = InputBox("Find what?", "Looking For?")
If It = Empty Then Exit Sub
With Worksheets("Main").Range("E12:E14")
Set Target = .Find(It, LookIn:=xlValues, SearchOrder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
If Not Target Is Nothing Then
FirstAddress = Target.Address '<< bookmark the start-point of the search
Do
'Do whatever you want, the messagebox is an example...
MsgBox "A " & It & " was found at " & Target.Address & " (" & Target.Value & ")"
Set Target = .FindNext(Target)
Loop Until Target Is Nothing Or Target.Address = FirstAddress
Else
MsgBox It & " Not Found"
End If
End With
End Sub[/vba]

[Cyberdude]

Thanx, John. I tried it and it didn't work. HOWEVER, I've been testing using dates since that is what I usually am searching for. I switched the data and search arg to all alpha values, and ... Voila! ... it worked!

So I changed your "It" from As String to As Date, and it did work. My original test version DOES have As Date, so at the moment I'm a little mystified, but I'm sure now that it's a formatting problem, so I'll pursue that.
Thanx again.

[Cyberdude]

OK, I finally got the FIND method to work BUT only on its terms. I'm searching for a date, and the ONLY way it would find the date is if the date list is formatted "mm/dd/yyyy". Why FIND would care how a date is formatted eludes me, but I had to format the date with 4 year digits.
It found it when I formatted as "(ddd) mm/dd/yyyy" but not if it was "(ddd) mm/dd/yy".
In fact the "mm/dd/yyyy" or "m/d/yyyy" always worked.

Now my question is, what does the FIND argument "SearchFormat" do for you, and how do you write the format value? I don't want my date list formatted "mm/dd/yyyy", so what can I do to get FIND to work with a different date format?

Later:
FIND is even more finicky than I thought. Change what I said about using "mm/dd/yyyy". Two "d's" works only if the search value specified 2 d's. It turns out that specifying one "d" will work for both two day digits and one digit in the search value, but not the reverse. So for the general case you must use "mm/d/yyyy". It doesn't seem to care about the month. Both a single "m" and a double "mm" works.

Why would anyone use this method??

[johnske]

Sid, have you also made the change that Bob suggested above? I think you'll find that works (along with the 'It As Date' declaration) i.e. make this change[vba] Set Target = .Find(It, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)[/vba]Merry Xmas

[johnske]

Hi Sid,

Had my christmas dinner and an idea to make the Find method a simple "one-liner" for most purposes. So here's my Christmas present to you and all others who want it.

Keep the two functions 'LookFor' and 'FindDates' handy for whenever you need to use "Find". The parameters in both these functions are simple - what you're looking for, and, the range where you want to look.

The LookFor function has the most common parameters set as: LookIn:=xlValues, SearchOrder:=xlByRows, LookAt:=xlPart, MatchCase:=False

The FindDates function has the most common parameters set as: LookIn:=xlFormulas, SearchOrder:=xlByRows

There are some examples given below on how to use these two functions, but here's the functions first...

[vba]Option Explicit

Public Function LookFor(ByRef What As String, Optional LookInRange As Range) As String

If LookInRange Is Nothing Then Set LookInRange = ActiveSheet.Cells
Dim Target As Range, FirstAddress As String
With LookInRange
Set Target = .Find(What, LookIn:=xlValues, SearchOrder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False)
If Not Target Is Nothing Then
FirstAddress = Target.Address '<< bookmark the start-point of the search
Do
If Target Is Nothing Or Target.Address = FirstAddress Then
LookFor = Target.Address & LookFor
Else
LookFor = Target.Address & ", " & LookFor
End If
Set Target = .FindNext(Target)
Loop Until Target Is Nothing Or Target.Address = FirstAddress
End If
End With

End Function



Public Function FindDates(ByRef FindDate As Date, Optional LookInRange As Range) As String

If LookInRange Is Nothing Then Set LookInRange = ActiveSheet.Cells
Dim Target As Range, FirstAddress As String
With LookInRange
Set Target = .Find(FindDate, LookIn:=xlFormulas, SearchOrder:=xlByRows)
If Not Target Is Nothing Then
FirstAddress = Target.Address '<< bookmark the start-point of the search
Do
If Target Is Nothing Or Target.Address = FirstAddress Then
FindDates = Target.Address & FindDates
Else
FindDates = Target.Address & ", " & FindDates
End If
Set Target = .FindNext(Target)
Loop Until Target Is Nothing Or Target.Address = FirstAddress
End If
End With

End Function[/vba]Here's some 'LookFor' demos
[vba]Sub LookFor_Demo1()

Dim MyValue As String

MyValue = InputBox("Find What?", "Looking For")
If MyValue = Empty Or MyValue = "vbCancel" Then Exit Sub

On Error GoTo NothingFound '< can't select nothing
Range(LookFor(MyValue)).Select

Exit Sub
NothingFound:
MsgBox "There are no entries for " & MyValue
End Sub



Sub LookFor_Demo2()

Dim MyValue As String

MyValue = InputBox("Find What?", "Looking For")
If MyValue = Empty Or MyValue = "vbCancel" Then Exit Sub

If LookFor(MyValue) = Empty Then
MsgBox "There are no entries for " & MyValue
Else
MsgBox MyValue & "'s found at " & LookFor(MyValue)
End If

End Sub



Sub LookFor_Demo3()

Dim MyValue As String

MyValue = InputBox("Find What?", "Looking For")
If MyValue = Empty Or MyValue = "vbCancel" Then Exit Sub

On Error GoTo NothingFound '< can't colour nothing
Range(LookFor(MyValue)).Interior.ColorIndex = 4

Exit Sub
NothingFound:
MsgBox "There are no entries for " & MyValue
End Sub[/vba]Here's a 'FindDates' demo
[vba]'do what you want below, the example given is just to find and select...
'you could use Range(FindDates(MyDate, [A1:F50])).Interior.ColorIndex = 4
'or maybe MsgBox FindDates(MyDate, Range("A1:E50")) ... etc.

Sub FindDates_Demo()

Dim MyDate As Date

MyDate = Application.InputBox("What date?", "Date", , , , , , 1)
If MyDate = Empty Or MyDate = vbCancel Then Exit Sub

On Error GoTo NothingFound '< can't select nothing
Range(FindDates(MyDate)).Select

Exit Sub
NothingFound:
MsgBox "There are no entries for " & MyDate
End Sub[/vba]Note that as the repeated property calls (such as Interior.ColorIndex = 4) have all been removed from the Do Loop and placed as a single action in the calling procedure this (without testing) SHOULD be faster than the normal usage for a "Find and do some things" in a procedure.

Merry Christmas!
John

[Cyberdude]

Hey, John, thanx for all the neat code. HOWEVER, your code proved what I had found separately:

Cyberdude's Law:
When searching for dates with .FIND, you have to specify the search arg in the same format that is used for the dates in the search list.

For example, my applications all use the date format:
"(ddd) mmm d, yy" .....(looks like "(Sun) Dec 25, 05").
If I specify the search arg as "12/25/05", it won't be found.
Even if I specify 12/25/2005, it won't be found. But it WILL find "(Sun) Dec 25, 05".

Now THAT's a constraint that they don't mention in HELP.
Incidentally, I found that I could omit the additional args
"LookIn:=xlValues, SearchOrder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False
and, when it worked, it was just as successful as it was with them.

Incidentally, there's a .FIND arg named SearchFormat for which I was unable to find an explanation. One wonders if that should be used when doing a search for a date. Do you happen to know what that arg is used for?

[Bob Phillips]

Incidentally, I found that I could omit the additional args
"LookIn:=xlValues, SearchOrder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False
and, when it worked, it was just as successful as it was with them.

That is because you are using the defaults. By definition, you don't have to specify defaults.

[johnske]

Hey, John, thanx for all the neat code...

Not a prob, BTW this is now in the KB and is now THREE separate functions "FindWhole", "FindPart", and "FindDates" with an example in the attachment of how to access the individual addresses in the address string.

Cyberdude's Law:
When searching for dates with .FIND, you have to specify the search arg in the same format that is used for the dates in the search list.

For example, my applications all use the date format:
"(ddd) mmm d, yy" .....(looks like "(Sun) Dec 25, 05").
If I specify the search arg as "12/25/05", it won't be found.
Even if I specify 12/25/2005, it won't be found. But it WILL find "(Sun) Dec 25, 05".

Now THAT's a constraint that they don't mention in HELP.

That's unusual, do you actually format the cells that way? If so, try changing your regional setting instead (Start > Settings > Control Panel > Regional Settings) and leaving the formats as the Office default setting.

Incidentally, there's a .FIND arg named SearchFormat for which I was unable to find an explanation. One wonders if that should be used when doing a search for a date. Do you happen to know what that arg is used for?

I have Office 2000 and that's not showing anywhere on it i.e. not known to me...

[Cyberdude]

That's unusual, do you actually format the cells that way?

No (LOL ), that's not my usual date format, but I DO use it extensively in my financial applications where I devote column "A" to a evergrowing list of dates when the stock market is open. Not all dates are present due to holidays etc., and it helps me spot when my date list extension logic didn't work correctly (rare). I definitely wouldn't like to have my regional settings with that format, but maybe I could change the regional settings upon entry to the applications. Hmmmm.

I tried to write a VLOOKUP yesterday searching for a date, and never could get it to work.

I DO appreciate the thought you've given to this problem. I have submited three articles on the care and feeding of dates in Excel, and I learned a lot from writing them, but apparently I haven't learned enough.