Find/Lookup data within hidden cells
 

I want to be able to select a cell within worksheet named "Summary" which is
an account #, and the macro searches down the account # on another existing
worksheet named "Daily Recap" within cell range A2:J22500
the data range in "Daily Recap" is grouped/hidden. i want the macro to take
me directly to that cell and open up the grouped rows (but only those rows.
not the other grouped rows on the worksheet) and show me the cell data.

Is this possible?? If not, do you know how to code the first part. It would
be so great to push a macro button (after selecting a specific cell) and
being automatically taken to the more detailed record of that account. I know
i can use the built-in FIND (control+F) function. If it's too hard to figure
out, can i just have a macro which will pop-up the FIND search function
window. Having a easy-to-use-and click Macro Button will be a nice dumb-down
feature for me & my co-workers when dealing with the massive range of data.

Thanks, Bryce

Find/Lookup data within hidden cells
 

Maybe this will help:

Sub Dtail()
Dim fRng As Range
Dim lRng As Range
Set lRng = ActiveCell
With Sheets("Daily Recap").Range("A2:J22500")
Set fRng = .Find(lRng.Value)
End With
If Not fRng Is Nothing Then
fRng.EntireRow.ShowDetail = True
Sheets("Daily Recap").Activate
fRng.Select
End If
End Sub

Regards
Rowan

Bryce wrote:
I want to be able to select a cell within worksheet named "Summary" which is
an account #, and the macro searches down the account # on another existing
worksheet named "Daily Recap" within cell range A2:J22500
the data range in "Daily Recap" is grouped/hidden. i want the macro to take
me directly to that cell and open up the grouped rows (but only those rows.
not the other grouped rows on the worksheet) and show me the cell data.

Is this possible?? If not, do you know how to code the first part. It would
be so great to push a macro button (after selecting a specific cell) and
being automatically taken to the more detailed record of that account. I know
i can use the built-in FIND (control+F) function. If it's too hard to figure
out, can i just have a macro which will pop-up the FIND search function
window. Having a easy-to-use-and click Macro Button will be a nice dumb-down
feature for me & my co-workers when dealing with the massive range of data.

Thanks, Bryce

Find/Lookup data within hidden cells
 

thanks rowan,

but if i have two identical account #s in that same column it send me to the
first account #.

is it possible to concatenate two cells (one being the account # in column B
and the other being the amount in column D). and by running the macro it will
search through the data range & then return the row which has the proper data
record? -that would be cool.

"Rowan" wrote:

Maybe this will help:

Sub Dtail()
Dim fRng As Range
Dim lRng As Range
Set lRng = ActiveCell
With Sheets("Daily Recap").Range("A2:J22500")
Set fRng = .Find(lRng.Value)
End With
If Not fRng Is Nothing Then
fRng.EntireRow.ShowDetail = True
Sheets("Daily Recap").Activate
fRng.Select
End If
End Sub

Regards
Rowan

Bryce wrote:
I want to be able to select a cell within worksheet named "Summary" which is
an account #, and the macro searches down the account # on another existing
worksheet named "Daily Recap" within cell range A2:J22500
the data range in "Daily Recap" is grouped/hidden. i want the macro to take
me directly to that cell and open up the grouped rows (but only those rows.
not the other grouped rows on the worksheet) and show me the cell data.

Is this possible?? If not, do you know how to code the first part. It would
be so great to push a macro button (after selecting a specific cell) and
being automatically taken to the more detailed record of that account. I know
i can use the built-in FIND (control+F) function. If it's too hard to figure
out, can i just have a macro which will pop-up the FIND search function
window. Having a easy-to-use-and click Macro Button will be a nice dumb-down
feature for me & my co-workers when dealing with the massive range of data.

Thanks, Bryce

Find/Lookup data within hidden cells
 

Hi Bryce

Assuming the account # is also in column B and value in column D on the
Recap sheet then try this:

Sub lkp()
Dim firstAddress As String
Dim fRng As Range
Dim lAcnt As String
Dim lVal As Double
lAcnt = Cells(ActiveCell.Row, 2).Value
lVal = Cells(ActiveCell.Row, 4).Value
With Sheets("Daily Recap").Range("B2:B22500")
Set fRng = .Find(lAcnt, LookIn:=xlFormulas)
If Not fRng Is Nothing Then
If fRng.Offset(0, 2).Value < lVal Then
firstAddress = fRng.Address
Do
Set fRng = .FindNext(fRng)
Loop While Not fRng Is Nothing _
And fRng.Address < firstAddress _
And fRng.Offset(0, 2).Value < lVal
End If
End If
End With
If Not fRng Is Nothing Then
fRng.EntireRow.ShowDetail = True
Sheets("Daily Recap").Activate
fRng.Activate
End If
End Sub

Regards
Rowan

Bryce wrote:
thanks rowan,

but if i have two identical account #s in that same column it send me to the
first account #.

is it possible to concatenate two cells (one being the account # in column B
and the other being the amount in column D). and by running the macro it will
search through the data range & then return the row which has the proper data
record? -that would be cool.

"Rowan" wrote:


Maybe this will help:

Sub Dtail()
Dim fRng As Range
Dim lRng As Range
Set lRng = ActiveCell
With Sheets("Daily Recap").Range("A2:J22500")
Set fRng = .Find(lRng.Value)
End With
If Not fRng Is Nothing Then
fRng.EntireRow.ShowDetail = True
Sheets("Daily Recap").Activate
fRng.Select
End If
End Sub

Regards
Rowan

Bryce wrote:

I want to be able to select a cell within worksheet named "Summary" which is
an account #, and the macro searches down the account # on another existing
worksheet named "Daily Recap" within cell range A2:J22500
the data range in "Daily Recap" is grouped/hidden. i want the macro to take
me directly to that cell and open up the grouped rows (but only those rows.
not the other grouped rows on the worksheet) and show me the cell data.

Is this possible?? If not, do you know how to code the first part. It would
be so great to push a macro button (after selecting a specific cell) and
being automatically taken to the more detailed record of that account. I know
i can use the built-in FIND (control+F) function. If it's too hard to figure
out, can i just have a macro which will pop-up the FIND search function
window. Having a easy-to-use-and click Macro Button will be a nice dumb-down
feature for me & my co-workers when dealing with the massive range of data.

Thanks, Bryce