| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using countif in a filtered range | Excel Worksheet Functions | |||
| Sum a filtered range | Excel Discussion (Misc queries) | |||
| copy only filtered range. | Excel Discussion (Misc queries) | |||
| traversing through a filtered range based on another filtered range | Excel Programming | |||
| filtered range | Excel Programming | |||
Linear Mode
