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,loo kat:=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




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



"Neal Zimm" wrote:

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.
--
Neal Z


"Joel" wrote:

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 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


"Neal Zimm" wrote:

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)