Mishell -
Thanks. Your explanation of the text property equating to xlValues clears
the fog.
I had thought xlValues kinda equated to range(xxx).value more than was
true.
I've adjusted the default optional values in the proc statement below for
the tool that I've built.

The boolean Whole and Formulas vars value look in and look at variables.

It's working pretty well now.

Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _
Optional AfterRng As Range = Nothing, Optional bFormulas As Boolean =
True, _
Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
' Return data ranges containing vFind.
' Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find. DupeRng not nothing holds
ADDITIONAL vFind's.
' lCount = count of cells in DupeRng. iAreas = count of areas in DupeRng.
' Inputs: InRng = search range, vFind = What to look for.
' bWhole, look in xlWhole, F = look in xlPart
' AfterRng, Nothing= proc values AfterRng as rightmost bottom cell so find
starts AT top left cell, NOT MSo default of next after top left.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, Debug.Print Sub's results


--
Neal Z


"Mishell" wrote:

Each cell in a worksheet has a Formula property, a Text property and a Value
Property. The Value property is ignored by the FIND command. The search of
the FIND command is made only in the Formula Property (LookIn:=xlFormulas)
or in the Text Property (LookIn:=xlValues). The Text property is dependant
of the View Format chosen for the cell. It does not necessarily contain the
real value of a number or of a date. If the cell does not have a formula,
then the Formula property contains the real value of the cell and this value
can then be found by the FIND command using LookIn:=xlFormulas.
If you are looking for a defined format of a value, use LookIn:=xlValues to
look in the Text property of the cells.
If you are looking for a value whatever the cell format is, use
LookIn:=xlFormulas to look in the Formula property of the cells.

The VarType of the searched item is also very important. The value of the
date Jan-04-2006 is 38721. To find the number 38721, give the searched item
the Double Type (CDbl(searchedItem)) or the String Type
(CStr(searchedItem)). To find the date corresponding to the value 38721,
give the searched item the Date Type (CDate(searchedItem)). The result will
include all the corresponding cells with any Date format, plus the
corresponding cells with a Text format but containing a string with the
"Short Date" format of the searched date.


Regards,

Mishell



"Neal Zimm" wrote in message
...
Hi Mishell - Well, the saga continues.
I built the proc below to isolate .Find and .Findnext.
I need to capture the first 'find' and to isolate any additional cells
with
the same
value as 'Dupes'.

Test cases #1 #2 #3 work swell without your "C" code.
#4 and #4A also work without your code.
#4B is where I hit the Jack Daniels, your code does not seem to matter.

I'm using only 5 test data cells, values and formats are below. In case
you'd like to run the proc.

I am millimeters away from making a work around in the 'real' generalized
sub as my permanent method. It's a plain old row loop using
If Varname = cells(row,col).value Then .... and it works great. Go
figure.

I'd appreciate any further thoughts. You should be able to copy
the sub below, key the data into a sheet, d1:d5, and run it.
Just remember to select 1 cell in the test data range in the sheet
for the AfterRng .

Thanks.
Neal Z.


Sub A_Test_Find()
Dim AfterRng As Range
Dim DupeRng As Range
Dim Found1Rng As Range
Dim InRng As Range
Dim Rng As Range
Dim lCount As Long
Dim LookAt As Integer
Dim LookIn As Integer
Dim sFirAdr As String
Dim vFind As Variant

' #1 string testing, note this worked without your CStr
' Code worked as expected for string vFind's.
' h1 thru h5 = abc, [cleared], abd, abe, abe
' Set InRng = Range("h1:h5")
' vFind = "abe": LookIn = xlValues: LookAt = xlWhole
' vFind = "ab": LookIn = xlValues: LookAt = xlPart

' #2 date testing, note, this worked without your CDate
' Question: WHY does .Find NOT work with xlValues ?
' f1:f5 "mmm d", f1=Aug 10, f2:f4=Aug 28, f5=Aug 10
' Set InRng = Range("f1:f5")
' vFind = DateValue("aug 28")
' LookIn = xlFormulas: LookAt = xlWhole

' #3 numeric testing, all values entered by hand, this worked without your
CDbl
' Question: Same not work with xlValues ?
' For a hand entered number debug.print of a range(xx).value
' or range(xx).formula show the same number.
' It worked just fine with test data below.
' d1:d5 "number" 2 decimals, d1:d3 = 12.34, d4:d5 = 1.56
' Set InRng = Range("d1:d5")
' vFind = Range("d1").Value '1.56 12.34 range("D1").value
' LookIn = xlFormulas: LookAt = xlWhole


' #4 numeric testing, 4 hand entered #'s into cells, 1 formula cell.
' Numeric cells NOT found.
' It's 'seen' value in the cell is 12.34.
' "d2" was found using xlPart and xlFormulas in a diff test case.

' d1:d5 "number" 2 decimals, d3 formula is: = d2
' d1:d2 = 12.34, d4:d5 = 1.56

' Set InRng = Range("d1:d5")
' vFind = 12.34 '1.56 12.34 range("D1").value "d2"
' LookAt = xlWhole
' LookIn = xlValues

' #4A This is screwy, using the value from the d3 cell with the formula,
' I was able to find d1 and d2 using xlFormulas, as expected. See #4B
' Your CDbl was not used.
' Set InRng = Range("d1:d5")
' vFind = Range("d3").Value
' LookAt = xlWhole
' LookIn = xlFormulas

' #4B The only change from 4A is xlValues for LookIn.
' With and without CDbl, no cell was found. Oh well.
' Same cell contents as #4.
Set InRng = Range("d1:d5")
vFind = Range("d3").Value
'vFind = 12.34 'still no go with this line
LookAt = xlWhole
LookIn = xlValues


Cells.Interior.ColorIndex = xlNone 'to test
Set AfterRng = Selection 'to test varying the after cell

With InRng
'AfterRng, If optional input Arg is nothing, this proc values
'AfterRng as rightmost bottom cell so find starts AT top left cell,
'NOT MSo default of next after top left.
If AfterRng Is Nothing Then _
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))

If IsNumeric(vFind) Then
vFind = CDbl(vFind)
ElseIf IsDate(vFind) Then
vFind = CDate(vFind)
Else
vFind = CStr(vFind)
End If

Set Rng = .Find(vFind, AfterRng, LookIn, LookAt)

If Not Rng Is Nothing Then
Set Found1Rng = Rng
sFirAdr = Found1Rng.Address
Found1Rng.Interior.ColorIndex = 35 'testing only

Do
Set Rng = .FindNext(Rng)

If Rng Is Nothing Or Rng.Address = sFirAdr Then
Exit Do
Else
lCount = lCount + 1
If lCount = 1 Then Set DupeRng = Rng _
Else Set DupeRng = Union(DupeRng, Rng)
End If

Loop While Not Rng Is Nothing And Rng.Address < sFirAdr
End If

End With

If Not DupeRng Is Nothing Then DupeRng.Interior.ColorIndex = 6 'testing
only
End Sub
--
Neal Z


"Mishell" wrote:

Hi Neal.

Add something like this before the FIND command.

If IsNumeric(vFind) Then
vFind = CDbl(vFind)
ElseIf IsDate(vFind) Then
vFind = CDate(vFind)
Else
vFind = CStr(vFind)
End If

And prefer xlFormulas to xlValues if you are looking for numbers or
dates.

Regards,

Mishell



"Neal Zimm" wrote in message
...
Hi -
I built the proc below as a tool using the .Find
method. I'm testing it.

It works OK when vFind is loaded with a string or an integer number.

It does NOT find numeric values such as 1401.61 or any

date values. The Mso help on .Find says "any data type"

for the variant find argument.

Examples prior to calling FindRngValues

Dim DtTest as date
Dim nValue as single
Dim vFind as variant


nvalue = range(whatever).value '1300.00 (cell format number, 2
decimals)
vFind = nvalue
Call .... ' values were found.

nvalue = range(whatever).value '1401.61 (cell format number, 2
decimals)
vFind = nvalue
Call .... ' values were NOT found, but I see them in the worksheet
cells.

Same for DtTest values where cell formatted as date, "m/d/yyyy"
Cell values were not found.

What can I do to find these types of data ? (I have row loops that
work,
but I like the flexibility of proc below)

Thanks,
Neal Z.



Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount
As
Long, _
Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean =
True,
_
Optional AfterRng As Range = Nothing, Optional LookIn As Integer =
xlValues, _
Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0)

'Return data ranges containing vFind.
' DupeRng is nothing on not found or no dupes.
' lCellCount = count of cells in DupeRng.
' iAreas = count of areas in DupeRng.
' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL
duplicate values.
' bWhole T= xlWhole F= xlPart,
' To NOT use top left of InRng as After parm, value input AfterRng arg
with
1 cell.
' LookIn xlValues or xlFormulas
' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T=
DupeRng
contains Found1Rng,
' iAreas and lCellCount are for the union'd DupeRng.

Dim Rng As Range
Dim FirAdr As String
Dim LookAt As Integer 'xlwhole or xlpart

'mainline start
Set DupeRng = Nothing
iAreas = 0
lCellCount = 0
Set Found1Rng = Nothing
If InRng Is Nothing Then Exit Sub
If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub
If bWhole Then LookAt = xlWhole Else LookAt = xlPart

With InRng
If AfterRng Is Nothing Then
Set Rng = .Find(vFind, , LookIn, LookAt)
Else
Set Rng = .Find(vFind, AfterRng, LookIn, LookAt)
End If

If Not Rng Is Nothing Then

Set Found1Rng = Rng
FirAdr = Found1Rng.Address

Do
Set Rng = .FindNext(Rng)
If Not Rng Is Nothing And Rng.Address < FirAdr Then
lCellCount = lCellCount + 1
If lCellCount = 1 Then