Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically sum a filter
Hi I have an excel spreadsheet with 2 buttons to filter and unfilter th data by part number. How can I get it to also show me a total number i stock for that part number. i.e When I search for part numbe VCP/3301/MP the spreadsheet shows say 3020 in 1 location and say 250 in another. I need it to automatically show me a total for thi product. The current code is as follows:- Option Explicit Private Sub CommandButton1_Click() UserForm1.Show End Sub Private Sub CommandButton2_Click() With Worksheets("Inventory") If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With End Sub Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Column = 6 Or .Column = 9 Or .Column = 10 Then If IsNumeric(.Value) Or .HasFormula = True Then Application.EnableEvents = False Range("F" & Target.Row).Value = Range("K" Target.Row).Value + Range("F" & Target.Row).Value Application.EnableEvents = True End If End If End With End Sub mack -- mack ----------------------------------------------------------------------- macke's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=53370 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically sum a filter
=subtotal(9,C2:C2000)
would give you the sum of the visible items in the filter for values in column C (as an example). In code you can use mysum = application.Subtotal(9,Activesheet.Autofilter.Rang e.Columns(3)) Not sure how the code you show relates to the question asked. -- Regards, Tom Ogilvy "macke" wrote: Hi I have an excel spreadsheet with 2 buttons to filter and unfilter the data by part number. How can I get it to also show me a total number in stock for that part number. i.e When I search for part number VCP/3301/MP the spreadsheet shows say 3020 in 1 location and say 2503 in another. I need it to automatically show me a total for this product. The current code is as follows:- Option Explicit Private Sub CommandButton1_Click() UserForm1.Show End Sub Private Sub CommandButton2_Click() With Worksheets("Inventory") If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With End Sub Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Column = 6 Or .Column = 9 Or .Column = 10 Then If IsNumeric(.Value) Or .HasFormula = True Then Application.EnableEvents = False Range("F" & Target.Row).Value = Range("K" & Target.Row).Value + Range("F" & Target.Row).Value Application.EnableEvents = True End If End If End With End Sub macke -- macke ------------------------------------------------------------------------ macke's Profile: http://www.excelforum.com/member.php...o&userid=32445 View this thread: http://www.excelforum.com/showthread...hreadid=533703 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically sum a filter
brilliant that works Thank you mack -- mack ----------------------------------------------------------------------- macke's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=53370 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i get advanced filter to automatically refresh | Excel Worksheet Functions | |||
Can you automatically filter for unique records? | Excel Worksheet Functions | |||
Filter data automatically by input | Excel Worksheet Functions | |||
automatically filter for unique records... or other suggestions | Excel Worksheet Functions | |||
Can I automatically hide or filter a row? | Excel Worksheet Functions |