Automatically sum a filter

[macke]

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

[Tom Ogilvy]

=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.Range.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
>
>

[macke]

brilliant that works

Thank you

macke