Hi -
I built the FindRngData Sub below as a tool.

While running TestIt, I was able to find data in hidden rows, [or
columns], which was a surprise, after reading some of the postings here.

I could not find any talk finding xlValues versus xlFormulas, and,
the data I'm looking for in my App is almost always hand entered.

After running some iterations of TestIt, I've concluded that:
Values must be found in not hidden cells.
Data in the formulas property can be found regardless of .Hidden .

Do I have this right ?

What are other pitfalls in the "hidden arena" of which I'm not aware ?

Thanks,
Neal Z.


Sub TestIt()
Const TxnTypCol = 3
Dim AllRowsRng As Range
Dim TxnTypColRng As Range
Dim TxnTypRng As Range
Dim TxnTyp As String
Dim ChkBkFirRow As Long, ChkBkLasRow As Long
Dim lCount As Long

If ActiveSheet.Name < "test" Then
MsgBox "ONLY test sheet", vbCritical, "TestIt"
Exit Sub
End If

ChkBkFirRow = 9: ChkBkLasRow = 24

Set AllRowsRng = Rows(ChkBkFirRow & ":" & ChkBkLasRow)
AllRowsRng.Rows.Hidden = True

Set TxnTypColRng = Range(Cells(ChkBkFirRow, TxnTypCol), _
Cells(ChkBkLasRow, TxnTypCol))

TxnTyp = "Dep"

Call FindRngData(TxnTypColRng, TxnTyp, TxnTypRng, lCount, _
bFormulas:=True) 'False looks for values

If Not TxnTypRng Is Nothing Then TxnTypRng.Rows.Hidden = False
' formula stuff showed up, values did not.
End Sub


Sub FindRngData(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional bOneRng As Boolean = True, Optional Found1Rng As Range = Nothing, _
Optional bWhole As Boolean = True, Optional AfterRng As Range = Nothing, _
Optional bFormulas As Boolean = True, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
'Return data range containing vFind.
'Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find.
' DupeRng, not nothing holds ADDITIONAL cells.
' lCount = qty cells in DupeRng. iAreas = DupeRng area count.
'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.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, T= print results

Dim Rng As Range
Dim sFirAdr As String
Dim LookAt As Integer
Dim LookIn As Integer

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

With InRng
If AfterRng Is Nothing Then
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
ElseIf AfterRng.Rows.Count < 1 Or _
AfterRng.Columns.Count < 1 Then
MsgBox "AfterRng is NOT 1 cell, " & AfterRng.Address, _
vbCritical, "Sub FindRngData"
End 'End.
End If

If IsNumeric(vFind) Then vFind = CDbl(vFind)

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

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

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

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count

If bDebugPrt Then
Debug.Print Cr & "FindRngData " & Now & " InRng " & InRng.Address
If bFormulas Then Debug.Print "LookIn xlFormulas" _
Else Debug.Print "LookIn xlValues"
Debug.Print "vFind " & vFind & " OneRng " & bOneRng & " lCount "
& lCount
If Not Found1Rng Is Nothing Then
Debug.Print "First: " & Found1Rng.Address
If DupeRng Is Nothing Then Debug.Print "NO Dupes" _
Else Debug.Print "Dupes: " & DupeRng.Address
Else
Debug.Print "vFind Not Found"
End If
End If
'mainline end
End Sub

--
Neal Z

Neal,

I started creating notes regarding the Find Method after struggling to
understand some of its parameters. My notes, as they relate to the LookIn
parameter, are as follows:

xlValues will look at the cell values, regardless of whether the value is
hard coded or linked (i.e. referenced to another cell)

xlValues will NOT look at values that are hidden (or that are in a "hidden"
location, such as an outlined group that is collapsed)

xlFormulas can operate as a work around to the hidden shortfall of xlValues
under one condition -- if the cell value is hard coded and hidden, xlFormulas
will pick it up. If the cell value is linked and hidden, xlFormulas will not
pick it up.

I haven't read your code, but I took a quick glance, literally. As a side
note, you can use Excel's built-in constants for your parameters. (The code
below is an example of using Excel's built-in constants. Using the built-in
constants is nice because as you type the function in VBA, the IDE will show
you the available constants to choose from for the specified parameter). I'm
not sure if this pertains to your scenario, so ignore the comment if it
doesn't -- when it comes to Find, I strongly recommend that you read the Find
Method help documentation carefully because it has some subtle nuances.

Best,

Matthew Herbert

Function FindExample(rngSearch As Range, varFindWhat As Variant, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional SearchOrder As XlSearchOrder = xlByRows, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional MatchCase As Boolean = False, _
Optional MatchByte As Boolean = False, _
Optional SearchFormat As Boolean = False) As Range

"Neal Zimm" wrote:


Hi -
I built the FindRngData Sub below as a tool.

While running TestIt, I was able to find data in hidden rows, [or
columns], which was a surprise, after reading some of the postings here.

I could not find any talk finding xlValues versus xlFormulas, and,
the data I'm looking for in my App is almost always hand entered.

After running some iterations of TestIt, I've concluded that:
Values must be found in not hidden cells.
Data in the formulas property can be found regardless of .Hidden .

Do I have this right ?

What are other pitfalls in the "hidden arena" of which I'm not aware ?

Thanks,
Neal Z.


Sub TestIt()
Const TxnTypCol = 3
Dim AllRowsRng As Range
Dim TxnTypColRng As Range
Dim TxnTypRng As Range
Dim TxnTyp As String
Dim ChkBkFirRow As Long, ChkBkLasRow As Long
Dim lCount As Long

If ActiveSheet.Name < "test" Then
MsgBox "ONLY test sheet", vbCritical, "TestIt"
Exit Sub
End If

ChkBkFirRow = 9: ChkBkLasRow = 24

Set AllRowsRng = Rows(ChkBkFirRow & ":" & ChkBkLasRow)
AllRowsRng.Rows.Hidden = True

Set TxnTypColRng = Range(Cells(ChkBkFirRow, TxnTypCol), _
Cells(ChkBkLasRow, TxnTypCol))

TxnTyp = "Dep"

Call FindRngData(TxnTypColRng, TxnTyp, TxnTypRng, lCount, _
bFormulas:=True) 'False looks for values

If Not TxnTypRng Is Nothing Then TxnTypRng.Rows.Hidden = False
' formula stuff showed up, values did not.
End Sub


Sub FindRngData(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional bOneRng As Boolean = True, Optional Found1Rng As Range = Nothing, _
Optional bWhole As Boolean = True, Optional AfterRng As Range = Nothing, _
Optional bFormulas As Boolean = True, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
'Return data range containing vFind.
'Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find.
' DupeRng, not nothing holds ADDITIONAL cells.
' lCount = qty cells in DupeRng. iAreas = DupeRng area count.
'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.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, T= print results

Dim Rng As Range
Dim sFirAdr As String
Dim LookAt As Integer
Dim LookIn As Integer

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

With InRng
If AfterRng Is Nothing Then
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
ElseIf AfterRng.Rows.Count < 1 Or _
AfterRng.Columns.Count < 1 Then
MsgBox "AfterRng is NOT 1 cell, " & AfterRng.Address, _
vbCritical, "Sub FindRngData"
End 'End.
End If

If IsNumeric(vFind) Then vFind = CDbl(vFind)

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

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

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

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count

If bDebugPrt Then
Debug.Print Cr & "FindRngData " & Now & " InRng " & InRng.Address
If bFormulas Then Debug.Print "LookIn xlFormulas" _
Else Debug.Print "LookIn xlValues"
Debug.Print "vFind " & vFind & " OneRng " & bOneRng & " lCount "
& lCount
If Not Found1Rng Is Nothing Then
Debug.Print "First: " & Found1Rng.Address
If DupeRng Is Nothing Then Debug.Print "NO Dupes" _
Else Debug.Print "Dupes: " & DupeRng.Address
Else
Debug.Print "vFind Not Found"
End If
End If
'mainline end
End Sub

--
Neal Z

"Neal Zimm" wrote in message
...

Hi -
I built the FindRngData Sub below as a tool.

While running TestIt, I was able to find data in hidden rows, [or
columns], which was a surprise, after reading some of the postings here.

I could not find any talk finding xlValues versus xlFormulas, and,
the data I'm looking for in my App is almost always hand entered.

After running some iterations of TestIt, I've concluded that:
Values must be found in not hidden cells.
Data in the formulas property can be found regardless of .Hidden .

Do I have this right ?

You are right. I confirm that the xlValues argument is looking in the
visible part of the cells values (as defined by the Format chosen for each
cell) and is also only looking in the unhidden rows only.


What are other pitfalls in the "hidden arena" of which I'm not aware ?

Good question.

Mishell


Thanks,
Neal Z.


Sub TestIt()
Const TxnTypCol = 3
Dim AllRowsRng As Range
Dim TxnTypColRng As Range
Dim TxnTypRng As Range
Dim TxnTyp As String
Dim ChkBkFirRow As Long, ChkBkLasRow As Long
Dim lCount As Long

If ActiveSheet.Name < "test" Then
MsgBox "ONLY test sheet", vbCritical, "TestIt"
Exit Sub
End If

ChkBkFirRow = 9: ChkBkLasRow = 24

Set AllRowsRng = Rows(ChkBkFirRow & ":" & ChkBkLasRow)
AllRowsRng.Rows.Hidden = True

Set TxnTypColRng = Range(Cells(ChkBkFirRow, TxnTypCol), _
Cells(ChkBkLasRow, TxnTypCol))

TxnTyp = "Dep"

Call FindRngData(TxnTypColRng, TxnTyp, TxnTypRng, lCount, _
bFormulas:=True) 'False looks for values

If Not TxnTypRng Is Nothing Then TxnTypRng.Rows.Hidden = False
' formula stuff showed up, values did not.
End Sub


Sub FindRngData(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional bOneRng As Boolean = True, Optional Found1Rng As Range =
Nothing, _
Optional bWhole As Boolean = True, Optional AfterRng As Range = Nothing,
_
Optional bFormulas As Boolean = True, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
'Return data range containing vFind.
'Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find.
' DupeRng, not nothing holds ADDITIONAL cells.
' lCount = qty cells in DupeRng. iAreas = DupeRng area count.
'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.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, T= print results

Dim Rng As Range
Dim sFirAdr As String
Dim LookAt As Integer
Dim LookIn As Integer

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

With InRng
If AfterRng Is Nothing Then
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
ElseIf AfterRng.Rows.Count < 1 Or _
AfterRng.Columns.Count < 1 Then
MsgBox "AfterRng is NOT 1 cell, " & AfterRng.Address, _
vbCritical, "Sub FindRngData"
End 'End.
End If

If IsNumeric(vFind) Then vFind = CDbl(vFind)

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

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

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

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count

If bDebugPrt Then
Debug.Print Cr & "FindRngData " & Now & " InRng " & InRng.Address
If bFormulas Then Debug.Print "LookIn xlFormulas" _
Else Debug.Print "LookIn xlValues"
Debug.Print "vFind " & vFind & " OneRng " & bOneRng & " lCount "
& lCount
If Not Found1Rng Is Nothing Then
Debug.Print "First: " & Found1Rng.Address
If DupeRng Is Nothing Then Debug.Print "NO Dupes" _
Else Debug.Print "Dupes: " & DupeRng.Address
Else
Debug.Print "vFind Not Found"
End If
End If
'mainline end
End Sub

--
Neal Z

Matthew -
Thanks. We agree.
I use much the same optional variables as you in the sub statement with
this exception, and I did read that your look at the code was brief.

I use boolean variables where there are two choices e.g.
optional bFormulas as boolean = true

in the code:
if bFormulas then LookIn = xlFormulas else LookIn = xlValues

I do this 'cuz I don't to have to remember -4123 versus -4163 or whatever,
when I type the call Macro(xxxx xxxx xxxx statement and the optional
stuff appears.

i can remember bFormulas; true or false better

Thanks again.
--
Neal Z


"Matthew Herbert" wrote:

Neal,

I started creating notes regarding the Find Method after struggling to
understand some of its parameters. My notes, as they relate to the LookIn
parameter, are as follows:

xlValues will look at the cell values, regardless of whether the value is
hard coded or linked (i.e. referenced to another cell)

xlValues will NOT look at values that are hidden (or that are in a "hidden"
location, such as an outlined group that is collapsed)

xlFormulas can operate as a work around to the hidden shortfall of xlValues
under one condition -- if the cell value is hard coded and hidden, xlFormulas
will pick it up. If the cell value is linked and hidden, xlFormulas will not
pick it up.

I haven't read your code, but I took a quick glance, literally. As a side
note, you can use Excel's built-in constants for your parameters. (The code
below is an example of using Excel's built-in constants. Using the built-in
constants is nice because as you type the function in VBA, the IDE will show
you the available constants to choose from for the specified parameter). I'm
not sure if this pertains to your scenario, so ignore the comment if it
doesn't -- when it comes to Find, I strongly recommend that you read the Find
Method help documentation carefully because it has some subtle nuances.

Best,

Matthew Herbert

Function FindExample(rngSearch As Range, varFindWhat As Variant, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional SearchOrder As XlSearchOrder = xlByRows, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional MatchCase As Boolean = False, _
Optional MatchByte As Boolean = False, _
Optional SearchFormat As Boolean = False) As Range

"Neal Zimm" wrote:


Hi -
I built the FindRngData Sub below as a tool.

While running TestIt, I was able to find data in hidden rows, [or
columns], which was a surprise, after reading some of the postings here.

I could not find any talk finding xlValues versus xlFormulas, and,
the data I'm looking for in my App is almost always hand entered.

After running some iterations of TestIt, I've concluded that:
Values must be found in not hidden cells.
Data in the formulas property can be found regardless of .Hidden .

Do I have this right ?

What are other pitfalls in the "hidden arena" of which I'm not aware ?

Thanks,
Neal Z.


Sub TestIt()
Const TxnTypCol = 3
Dim AllRowsRng As Range
Dim TxnTypColRng As Range
Dim TxnTypRng As Range
Dim TxnTyp As String
Dim ChkBkFirRow As Long, ChkBkLasRow As Long
Dim lCount As Long

If ActiveSheet.Name < "test" Then
MsgBox "ONLY test sheet", vbCritical, "TestIt"
Exit Sub
End If

ChkBkFirRow = 9: ChkBkLasRow = 24

Set AllRowsRng = Rows(ChkBkFirRow & ":" & ChkBkLasRow)
AllRowsRng.Rows.Hidden = True

Set TxnTypColRng = Range(Cells(ChkBkFirRow, TxnTypCol), _
Cells(ChkBkLasRow, TxnTypCol))

TxnTyp = "Dep"

Call FindRngData(TxnTypColRng, TxnTyp, TxnTypRng, lCount, _
bFormulas:=True) 'False looks for values

If Not TxnTypRng Is Nothing Then TxnTypRng.Rows.Hidden = False
' formula stuff showed up, values did not.
End Sub


Sub FindRngData(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional bOneRng As Boolean = True, Optional Found1Rng As Range = Nothing, _
Optional bWhole As Boolean = True, Optional AfterRng As Range = Nothing, _
Optional bFormulas As Boolean = True, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
'Return data range containing vFind.
'Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find.
' DupeRng, not nothing holds ADDITIONAL cells.
' lCount = qty cells in DupeRng. iAreas = DupeRng area count.
'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.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, T= print results

Dim Rng As Range
Dim sFirAdr As String
Dim LookAt As Integer
Dim LookIn As Integer

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

With InRng
If AfterRng Is Nothing Then
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
ElseIf AfterRng.Rows.Count < 1 Or _
AfterRng.Columns.Count < 1 Then
MsgBox "AfterRng is NOT 1 cell, " & AfterRng.Address, _
vbCritical, "Sub FindRngData"
End 'End.
End If

If IsNumeric(vFind) Then vFind = CDbl(vFind)

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

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

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

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count

If bDebugPrt Then
Debug.Print Cr & "FindRngData " & Now & " InRng " & InRng.Address
If bFormulas Then Debug.Print "LookIn xlFormulas" _
Else Debug.Print "LookIn xlValues"
Debug.Print "vFind " & vFind & " OneRng " & bOneRng & " lCount "
& lCount
If Not Found1Rng Is Nothing Then
Debug.Print "First: " & Found1Rng.Address
If DupeRng Is Nothing Then Debug.Print "NO Dupes" _
Else Debug.Print "Dupes: " & DupeRng.Address
Else
Debug.Print "vFind Not Found"
End If
End If
'mainline end
End Sub

--
Neal Z

Mish -
Not a bad question, huh ? Open ended questions are tough.

Following up on our prior discussion, I have given up on using .find with
numeric values that may or may not be integer values. I just can't get it to
work.

Falling back to row loops, my rows are not that large in number so the
efficiency loss will not be too large.

Be well,
Neal



--
Neal Z


"Mishell" wrote:


"Neal Zimm" wrote in message
...

Hi -
I built the FindRngData Sub below as a tool.

While running TestIt, I was able to find data in hidden rows, [or
columns], which was a surprise, after reading some of the postings here.

I could not find any talk finding xlValues versus xlFormulas, and,
the data I'm looking for in my App is almost always hand entered.

After running some iterations of TestIt, I've concluded that:
Values must be found in not hidden cells.
Data in the formulas property can be found regardless of .Hidden .

Do I have this right ?

You are right. I confirm that the xlValues argument is looking in the
visible part of the cells values (as defined by the Format chosen for each
cell) and is also only looking in the unhidden rows only.


What are other pitfalls in the "hidden arena" of which I'm not aware ?

Good question.

Mishell


Thanks,
Neal Z.


Sub TestIt()
Const TxnTypCol = 3
Dim AllRowsRng As Range
Dim TxnTypColRng As Range
Dim TxnTypRng As Range
Dim TxnTyp As String
Dim ChkBkFirRow As Long, ChkBkLasRow As Long
Dim lCount As Long

If ActiveSheet.Name < "test" Then
MsgBox "ONLY test sheet", vbCritical, "TestIt"
Exit Sub
End If

ChkBkFirRow = 9: ChkBkLasRow = 24

Set AllRowsRng = Rows(ChkBkFirRow & ":" & ChkBkLasRow)
AllRowsRng.Rows.Hidden = True

Set TxnTypColRng = Range(Cells(ChkBkFirRow, TxnTypCol), _
Cells(ChkBkLasRow, TxnTypCol))

TxnTyp = "Dep"

Call FindRngData(TxnTypColRng, TxnTyp, TxnTypRng, lCount, _
bFormulas:=True) 'False looks for values

If Not TxnTypRng Is Nothing Then TxnTypRng.Rows.Hidden = False
' formula stuff showed up, values did not.
End Sub


Sub FindRngData(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional bOneRng As Boolean = True, Optional Found1Rng As Range =
Nothing, _
Optional bWhole As Boolean = True, Optional AfterRng As Range = Nothing,
_
Optional bFormulas As Boolean = True, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
'Return data range containing vFind.
'Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find.
' DupeRng, not nothing holds ADDITIONAL cells.
' lCount = qty cells in DupeRng. iAreas = DupeRng area count.
'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.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, T= print results

Dim Rng As Range
Dim sFirAdr As String
Dim LookAt As Integer
Dim LookIn As Integer

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

With InRng
If AfterRng Is Nothing Then
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
ElseIf AfterRng.Rows.Count < 1 Or _
AfterRng.Columns.Count < 1 Then
MsgBox "AfterRng is NOT 1 cell, " & AfterRng.Address, _
vbCritical, "Sub FindRngData"
End 'End.
End If

If IsNumeric(vFind) Then vFind = CDbl(vFind)

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

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

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

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count

If bDebugPrt Then
Debug.Print Cr & "FindRngData " & Now & " InRng " & InRng.Address
If bFormulas Then Debug.Print "LookIn xlFormulas" _
Else Debug.Print "LookIn xlValues"
Debug.Print "vFind " & vFind & " OneRng " & bOneRng & " lCount "
& lCount
If Not Found1Rng Is Nothing Then
Debug.Print "First: " & Found1Rng.Address
If DupeRng Is Nothing Then Debug.Print "NO Dupes" _
Else Debug.Print "Dupes: " & DupeRng.Address
Else
Debug.Print "vFind Not Found"
End If
End If
'mainline end
End Sub

--
Neal Z

I do this 'cuz I don't to have to remember -4123 versus -4163 or whatever,
if bFormulas then LookIn = xlFormulas else LookIn = xlValues

Don't declare the bFormulas parameter to your function as a Boolean.
Declare the variable as the enum type that contains xlFormulas and
xlValues. Doing so will give you Intellisense support when writing the
call to the function. E.g,

Function Whatever(blah, blah, blah, LookIn As XlFindLookIn)

When you are typing in the call to Whatever and you get to the LookIn
parameter, you'll get a drop down list of the valid values for that
parameter. No need to memorize any numbers -- just pick from the list.
Note that it is perfectly legal to assign any numeric value to an enum
type, even if that enum type doesn't define that value. For example,
it is perfectly legal to use

Whatever(LookIn:=1234)

even though 1234 is not an enumerated value of XlFindLookIn. You won't
get a compiler error and you won't get a run time error. What the code
does with an invalid value is anyone's guess, but it will run in one
fashion or another. (In VBNET, you get an IsDefined method to test
whether a value is valid for a specific enum, but sadly you don't get
that in VBA.)

In addition to using the built in enum data types, you can create your
own and get intellisense support for working with them. See
http://www.cpearson.com/Excel/Enums.aspx for more detail.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Aug 2009 17:06:02 -0700, Neal Zimm
wrote:

Matthew -
Thanks. We agree.
I use much the same optional variables as you in the sub statement with
this exception, and I did read that your look at the code was brief.

I use boolean variables where there are two choices e.g.
optional bFormulas as boolean = true

in the code:
if bFormulas then LookIn = xlFormulas else LookIn = xlValues

I do this 'cuz I don't to have to remember -4123 versus -4163 or whatever,
when I type the call Macro(xxxx xxxx xxxx statement and the optional
stuff appears.

i can remember bFormulas; true or false better

Thanks again.