.Find method with numeric and date values, Aug2009

N

Neal Zimm

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 re: 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
Set DupeRng = Rng
Else
Set DupeRng = Union(DupeRng, Rng)
End If
End If
Loop Until Rng Is Nothing Or Rng.Address = FirAdr
End If
End With


If Not Found1Rng Is Nothing And bOneRng Then
If DupeRng Is Nothing Then
Set DupeRng = Found1Rng
lCellCount = 1
Else
Set DupeRng = Union(Found1Rng, DupeRng)
lCellCount = lCellCount + 1
End If
End If

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count
'mainline end
End Sub
 
J

Joel

I modified the code the way microsoft would write the code. You didn't
specify lookin:=values and lookat:=xlwhole. I removed an unecessay IF
statement in the loop and changed "Loop until" to "loop while", and added a
NOT.

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 re: 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(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole)
Else
Set Rng = .Find(what:=vFind, after:=AfterRng, LookIn:=xlvalues,
LookAt:=xlvalues)
End If

If Not Rng Is Nothing Then

Set Found1Rng = Rng
FirAdr = Found1Rng.Address

Do
Set Rng = .FindNext(after:=Rng)
lCellCount = lCellCount + 1
If lCellCount = 1 Then
Set DupeRng = Rng
Else
Set DupeRng = Union(DupeRng, Rng)
End If
End If
Loop while not Rng Is Nothing Or Rng.Address = FirAdr
End If
End With


If Not Found1Rng Is Nothing And bOneRng Then
If DupeRng Is Nothing Then
Set DupeRng = Found1Rng
lCellCount = 1
Else
Set DupeRng = Union(Found1Rng, DupeRng)
lCellCount = lCellCount + 1
End If
End If

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count
'mainline end
End Sub
 
N

Neal Zimm

Hi Joel, Thanks for the effort, I think.

This will be harsh, but I'm writing this paragraph after completing the
rest
of what you're about to read. This is the first time I've received advice
on this
community that's just bad and wrong. Sorry, I think you took very little
time
and care with your response. It seems you do not know very much about
optional
passing of argument values and your VBA syntax looked sloppy. You do not
have
to respond to this posting, but I would be happy to discuss any of it if
you think it's
wrong.

I'm sorry I did not make it clearer in my posting what my real problem is.

The proc DOES find strings very nicely. When vFind is loaded with a
numeric value
like 1300 it finds 1st occurence and dupes just fine.

When vFind is loaded with a value like 1401.61, it's first cell is
SOMETIMES
NOT found and additional cells with this value are not found.

I'm guessing it has something to do with the way the data is stored in the
sheet, or the way I'm doing stuff like: vFind = Varname 'before the call.

I DON'T think your changes have much to do with the above problem, AND,
sorry
to say, Many of them are Wrong or not needed. (I can't think of a way to
"change"
vFind inside the proc) .

I have problems with some of your suggestions, but I'm still learning about
..find and .findnext, please comment on them so I can be sure. Thanks.

"You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ.

Proc argument: Optional bWhole As Boolean = True
mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart

Proc argument: Optional LookIn As Integer = xlValues
Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values
Call FindRngValues(.......,xlFormulas,,)

I want the Sub to be able to find values OR formulas, (or constants in the
cells) which are the numbers relating to my problem.

"I modified the code the way microsoft would write the code."
MSo provides examples, which I changed to meet MY functional needs, especially
the ability to vary the meaning of the returned Found1Rng and DupeRng
arguments.
DupeRng either contains Found1Rng or not. See bOneRng argument.

"I modified the code the way microsoft would write the code."
My understanding of using named arguments is that you NEED them if you are
leaving standard argument values out, or changing the left to right order of
input.

If AfterRng Is Nothing Then
'Why your change?? My ", ," leaves out the After argument, AND, you have
' FORCED this proc to ALWAYS look for WHOLE cell VALUES; NOT my intention,
' hence my use of the values in the LookIn and LookAt arguments for
' lookin values/formulas and lookat whole/part

Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) 'Joel
Set Rng = .Find(vFind, , LookIn, LookAt) 'Neal

Else 'Joel
Set Rng = .Find(what:=vFind, after:=AfterRng, _
LookIn:=xlvalues,LookAt:=xlvalues)
Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) 'Neal

'Again, why your change ??? Since the first 4 values in .Find are what,
after,
'lookin, lookat I saw no need for using named arguments, and you were
'careless here, you used xlValues for both LookIn: and LookAt:
End If

"I removed an unecessay IF statement in the loop."
I'm pretty damn sure you are wrong, Joel, see my comments in the code. I am
not
making these changes. As I said in my problem statement, this proc is
working just fine when vFind contains a string value.

If Not Rng Is Nothing Then '1st find was successful

Set Found1Rng = Rng 'capture 1st range
FirAdr = Found1Rng.Address 'capture address of 1st found range

Do 'try to find 2nd and more
Set Rng = .FindNext(after:=Rng) 'after:= is redundant, it's the
ONLY argument

'Joel REMOVED line below
' If Not Rng Is Nothing And Rng.Address <> FirAdr Then
' Well, you've blasted to smithereens a key part of my logic.
' 1. The code below will run whether or NOT .FindNext found
something.
' I want the cell count and DupeRng ONLY for .FindNext that
results in a
' find which typically is not the 1st find.
' 2. Further, suppose .FindNext works once, and then finds
nothing more.
' Rng will be nothing and the DupeRng union statement will
blow up as it
' requires not nothing arguments.

lCellCount = lCellCount + 1
If lCellCount = 1 Then
Set DupeRng = Rng
Else
Set DupeRng = Union(DupeRng, Rng)
End If
End If 'Your ERROR, you removed the If, but NOT the end if.

Loop while not Rng Is Nothing Or Rng.Address = FirAdr 'changed from
Until
End if

Last BUT not least, Do, Loop Until versus Do While, Loop can be a choice
of style as well as substance. I understand the change to While because
you
took out the If statement. BUT "While" with " Or Rng.Address = FirAdr"
is
DREADFULLY WRONG. The loop is supposed to QUIT when .findnext wraps
around and finds the 1st value again.

This change will cause a NEVER ENDING loop when .findnext finds at
least a
second occurence of the vFind value. If you can get your code to run,
try it out.
 
M

Mishell

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
 
J

Joel

See comments below

Hi Joel, Thanks for the effort, I think.

This will be harsh, but I'm writing this paragraph after completing the
rest
of what you're about to read. This is the first time I've received advice
on this
community that's just bad and wrong. Sorry, I think you took very little
time
and care with your response. It seems you do not know very much about
optional
passing of argument values and your VBA syntax looked sloppy. You do not
have
to respond to this posting, but I would be happy to discuss any of it if
you think it's
wrong.

------------------------------------------------------------------------------

1)
I know a lot about arguement passing. I'm not sure what VBA will do if you
have an ariguenemt with no options like lookin instead of lookin:=xlvalues.
You don't need the lookin as long as you have the correct number of commas.
find is defined as

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte, SearchFormat)

if you don't need the after you can skip by just having the commas

expression.Find(What,, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte, SearchFormat)

and the parameter don't have to be in the order listed as long as you
include the type. for example

set c = columns("C").find(what:="abc",lookin:=xlvalues,lookat:=xlwhole)

is the same as

set c = columns("C").find(what:="abc",lookat:=xlwhole, lookin:=xlvalues)

or

set c = columns("C").find("abc",,xlvalues, xlwhole)

You did this

set c = columns("C").find("abc",,lookin, lookat)


------------------------------------------------------------------------------

I'm sorry I did not make it clearer in my posting what my real problem is.

The proc DOES find strings very nicely. When vFind is loaded with a
numeric value
like 1300 it finds 1st occurence and dupes just fine.

When vFind is loaded with a value like 1401.61, it's first cell is
SOMETIMES
NOT found and additional cells with this value are not found.

I'm guessing it has something to do with the way the data is stored in the
sheet, or the way I'm doing stuff like: vFind = Varname 'before the call.

----------------------------------------------------------------------------

2) The worksheet truncates the stored number that gets displayed but real
uses the full number in memory

if you have 1401.61123456789 and you have a format that display 2 decimal
digits you will see
1401.61 but the full number is still in memory and you must a find is
looking at the entire number.

----------------------------------------------------------------------------




I DON'T think your changes have much to do with the above problem, AND,
sorry
to say, Many of them are Wrong or not needed. (I can't think of a way to
"change"
----------------------------------------------------------------------------
I didn't see anything drastic with the code you except you had an unecessary
IF statement.

If Not Rng Is Nothing And Rng.Address <> FirAdr Then


I made other changes to the code to use methods that I was 100% sure would
work that I used hundreds of times before. Since you were having problems I
thought it was best to use know methods than to attempt to figure out if you
non-standard code would work. I only saying you code is non-standard (not
wrong) because it doesn't agree with the example that is given in the VBA
help menu. I don't alway like the methods that are givin in the VBA help and
use my own version when I think my code is more understandable. when I have
problems with my code I usually go back to simple methods to debug the
problem that I know will work. That all I was attempting in your case.
----------------------------------------------------------------------------


vFind inside the proc) .

I have problems with some of your suggestions, but I'm still learning about
..find and .findnext, please comment on them so I can be sure. Thanks.

"You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ.

Proc argument: Optional bWhole As Boolean = True
mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart

Proc argument: Optional LookIn As Integer = xlValues
Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values
Call FindRngValues(.......,xlFormulas,,)

I want the Sub to be able to find values OR formulas, (or constants in the
cells) which are the numbers relating to my problem.

--------------------------------------------------------------------
3)
Xlvalues can be one of three different options

a) Comments (not constants)
b) values
c) formulas - no sure what they mean by this. I think it is looking for
stating in the formula and not the value in the cell. the default is
variant. I have no idea what variant has to do with comments and formulas.
this really doesn't make sense. that is why I like to go back to code that I
know works because the documentation often is confusing. Microsoft should
hire better to to write the documentation.


--------------------------------------------------------------------

"I modified the code the way microsoft would write the code."
MSo provides examples, which I changed to meet MY functional needs, especially
the ability to vary the meaning of the returned Found1Rng and DupeRng
arguments.
DupeRng either contains Found1Rng or not. See bOneRng argument.

"I modified the code the way microsoft would write the code."
My understanding of using named arguments is that you NEED them if you are
leaving standard argument values out, or changing the left to right order of
input.

If AfterRng Is Nothing Then
'Why your change?? My ", ," leaves out the After argument, AND, you have
' FORCED this proc to ALWAYS look for WHOLE cell VALUES; NOT my intention,
' hence my use of the values in the LookIn and LookAt arguments for
' lookin values/formulas and lookat whole/part

Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) 'Joel
Set Rng = .Find(vFind, , LookIn, LookAt) 'Neal

Else 'Joel
Set Rng = .Find(what:=vFind, after:=AfterRng, _
LookIn:=xlvalues,LookAt:=xlvalues)
Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) 'Neal

'Again, why your change ??? Since the first 4 values in .Find are what,
after,
'lookin, lookat I saw no need for using named arguments, and you were
'careless here, you used xlValues for both LookIn: and LookAt:
End If
---------------------------------------------------------------------
4)
sorry for the typo


----------------------------------------------------------------------

"I removed an unecessay IF statement in the loop."
I'm pretty damn sure you are wrong, Joel, see my comments in the code. I am
not
making these changes. As I said in my problem statement, this proc is
working just fine when vFind contains a string value.

If Not Rng Is Nothing Then '1st find was successful

Set Found1Rng = Rng 'capture 1st range
FirAdr = Found1Rng.Address 'capture address of 1st found range

Do 'try to find 2nd and more
Set Rng = .FindNext(after:=Rng) 'after:= is redundant, it's the
ONLY argument

'Joel REMOVED line below
' If Not Rng Is Nothing And Rng.Address <> FirAdr Then
' Well, you've blasted to smithereens a key part of my logic.
' 1. The code below will run whether or NOT .FindNext found
something.
' I want the cell count and DupeRng ONLY for .FindNext that
results in a
' find which typically is not the 1st find.
' 2. Further, suppose .FindNext works once, and then finds
nothing more.
' Rng will be nothing and the DupeRng union statement will
blow up as it
' requires not nothing arguments.

lCellCount = lCellCount + 1
If lCellCount = 1 Then
Set DupeRng = Rng
Else
Set DupeRng = Union(DupeRng, Rng)
End If
End If 'Your ERROR, you removed the If, but NOT the end if.

Loop while not Rng Is Nothing Or Rng.Address = FirAdr 'changed from
Until
End if

Last BUT not least, Do, Loop Until versus Do While, Loop can be a choice
of style as well as substance. I understand the change to While because
you
took out the If statement. BUT "While" with " Or Rng.Address = FirAdr"
is
DREADFULLY WRONG. The loop is supposed to QUIT when .findnext wraps
around and finds the 1st value again.

This change will cause a NEVER ENDING loop when .findnext finds at
least a
second occurence of the vFind value. If you can get your code to run,
try it out.
------------------------------------------------------------------------------
5) the only time the last test below is needed is when the obj (RNG) is only
one item (cell)

Loop while not Rng Is Nothing Or Rng.Address <> FirAdr 'changed from
 
N

Neal Zimm

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 re: 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
 
M

Mishell

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 said:
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 re: 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 said:
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
Click to expand...
Click to expand...
 
N

Neal Zimm

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 re: 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 said:
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 said:
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 re: 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 said:
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



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 re: 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
Click to expand...
Click to expand...
Click to expand...
 
M

Mishell

Thanks to you Neal. Glad if I could help you with the FIND issue.

Regards,

Mishell

Neal Zimm said:
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 re: 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 said:
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 said:
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 re: 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


:

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



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 re: 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
Click to expand...
Click to expand...
Click to expand...
 
N

Neal Zimm

Joel -
I apologize for getting so hot before. It stemmed from the fact that the
MSo help example for .Find and .Findnext contains an error when I finally ran
it. I didn't find that out until after I responded to your post.

Turns out the real cause is the value I put into the vFind variable, and
as it turns out, valuing the LookIn argument to xlValues, when for most
numeric data, it's far better to LookIn xlFormulas. (At least if the value
your search for is not the result of a 'real' formula in the cell. That's the
situation in my App.

Thanks again for the considerable time you spent on this.

The working version of the find and next loop is below.
Neal Z

With InRng
If AfterRng Is Nothing Then 'default to last cell in range to search
the 1st.
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
End If

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

If Not Rng Is Nothing Then
Set Found1Rng = Rng
sFirAdr = Found1Rng.Address
Do While Not Rng is Nothing
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
End If
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

.Find .Hidden Values versus Formulas Aug2009 5
FindNext and more than 1 Area 2
Extract name, address and ph# 8
Word macro that can call cells out of Excel for a find and replace 0
Macro to find and copy to another sheet 4
Code does not error but no copy either 4
Placing the MsgBox "No ID found" statement in my code?? 12
I want to find the location of the a word in an Excel document. 0

Top