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

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

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