Max Value within filtered range
 

I am trying to return the MAx value in a filtered column using the following,
but I cannot get any data into my spreadsheet.
Help!

Thanks
J

xlApp.Selection.AutoFilter
xlApp.Selection.AutoFilter Field:=26, Criteria1:="FALSE"
'==================== Check For Earned Value Numbers
========================================
xlApp.Sheets("Tasks").Select
z = xlApp.ActiveSheet.UsedRange.Row - 1 +
xlApp.ActiveSheet.UsedRange.Rows.Count
xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12)).Select
xlApp.Sheets("Top Sheet").Select
xlApp.Range("G19") = xlApp.WorksheetFunction.Max(xlRng)
xlApp.Sheets("Tasks").Select
xlApp.Selection.AutoFilter

Max Value within filtered range
 

=subtotal(4,range)

which can be used in code as well

mymax = application.Subtotal(4,Range("B2:B100"))

--
Regards,
Tom Ogilvy

"qed" wrote in message
...
I am trying to return the MAx value in a filtered column using the
following,
but I cannot get any data into my spreadsheet.
Help!

Thanks
J

xlApp.Selection.AutoFilter
xlApp.Selection.AutoFilter Field:=26, Criteria1:="FALSE"
'==================== Check For Earned Value Numbers
========================================
xlApp.Sheets("Tasks").Select
z = xlApp.ActiveSheet.UsedRange.Row - 1 +
xlApp.ActiveSheet.UsedRange.Rows.Count
xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12)).Select
xlApp.Sheets("Top Sheet").Select
xlApp.Range("G19") = xlApp.WorksheetFunction.Max(xlRng)
xlApp.Sheets("Tasks").Select
xlApp.Selection.AutoFilter

Max Value within filtered range
 

I tried the subtotal method and I'm getting a #Value Error. What am I doing
wrong?

Jeff
"Tom Ogilvy" wrote in message
...
=subtotal(4,range)

which can be used in code as well

mymax = application.Subtotal(4,Range("B2:B100"))

--
Regards,
Tom Ogilvy

"qed" wrote in message
...
I am trying to return the MAx value in a filtered column using the
following,
but I cannot get any data into my spreadsheet.
Help!

Thanks
J

xlApp.Selection.AutoFilter
xlApp.Selection.AutoFilter Field:=26, Criteria1:="FALSE"
'==================== Check For Earned Value Numbers
========================================
xlApp.Sheets("Tasks").Select
z = xlApp.ActiveSheet.UsedRange.Row - 1 +
xlApp.ActiveSheet.UsedRange.Rows.Count
xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12)).Select
xlApp.Sheets("Top Sheet").Select
xlApp.Range("G19") = xlApp.WorksheetFunction.Max(xlRng)
xlApp.Sheets("Tasks").Select
xlApp.Selection.AutoFilter

Max Value within filtered range
 

What's in B2:B100?

Any errors?



Buster wrote:

I tried the subtotal method and I'm getting a #Value Error. What am I doing
wrong?

Jeff
"Tom Ogilvy" wrote in message
...
=subtotal(4,range)

which can be used in code as well

mymax = application.Subtotal(4,Range("B2:B100"))

--
Regards,
Tom Ogilvy

"qed" wrote in message
...
I am trying to return the MAx value in a filtered column using the
following,
but I cannot get any data into my spreadsheet.
Help!

Thanks
J

xlApp.Selection.AutoFilter
xlApp.Selection.AutoFilter Field:=26, Criteria1:="FALSE"
'==================== Check For Earned Value Numbers
========================================
xlApp.Sheets("Tasks").Select
z = xlApp.ActiveSheet.UsedRange.Row - 1 +
xlApp.ActiveSheet.UsedRange.Rows.Count
xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12)).Select
xlApp.Sheets("Top Sheet").Select
xlApp.Range("G19") = xlApp.WorksheetFunction.Max(xlRng)
xlApp.Sheets("Tasks").Select
xlApp.Selection.AutoFilter



--

Dave Peterson

Max Value within filtered range
 

set xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12))
xlApp.Sheets("Top Sheet").Select
xlApp.Range("G19") = xlApp.WorksheetFunction.Subtotal(4,xlRng)

--
Regards,
Tom Ogilvy

"Buster" wrote in message
...
I tried the subtotal method and I'm getting a #Value Error. What am I
doing
wrong?

Jeff
"Tom Ogilvy" wrote in message
...
=subtotal(4,range)

which can be used in code as well

mymax = application.Subtotal(4,Range("B2:B100"))

--
Regards,
Tom Ogilvy

"qed" wrote in message
...
I am trying to return the MAx value in a filtered column using the
following,
but I cannot get any data into my spreadsheet.
Help!

Thanks
J

xlApp.Selection.AutoFilter
xlApp.Selection.AutoFilter Field:=26, Criteria1:="FALSE"
'==================== Check For Earned Value Numbers
========================================
xlApp.Sheets("Tasks").Select
z = xlApp.ActiveSheet.UsedRange.Row - 1 +
xlApp.ActiveSheet.UsedRange.Rows.Count
xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12)).Select
xlApp.Sheets("Top Sheet").Select
xlApp.Range("G19") = xlApp.WorksheetFunction.Max(xlRng)
xlApp.Sheets("Tasks").Select
xlApp.Selection.AutoFilter