need a variation in VBA for subtotals

[CJ]

Hi all
I am very new to VBA and don't see a site for Excel VBA beginners. Thats
why I am posting here.
Hope you can help me.

Below in an example

I recieve a word document weekly and cut and paste the following in an excel
spreadsheet. It is pasted into the same Columns headings and always starts
in the same place G3. Only the number of rows may change with the qty of
info

I can't change the format of the speadsheet , I need to key in other infor
and then pass it along

I have to manually click the sum function in the J column where the xxx are
to find the toal parts used for the week

Info is always already sorted alphabetically by part no. The subtotals
function only puts the totals above or below the information. What I need
is a sum or subtotal in Col J - tot qty , where the xxxx are, in the Last
cell before the part number changes. Is this possible to do with VBA

Any help will be appriciated - Thanks


col G H I J
Part No date qty used tot qty inv no inv
date
---------- ----- ---------- --------- -------
------------
A321 5/1 20
A321 5/3 54 xxx
S589 5/1 52
S589 5/2 19
S589 5/3 16 xxx
X568 5/3 9 xxx

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim iTotal As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
iTotal = iLastRow
For i = iLastRow To 2 Step -1
If Cells(i, "G").Value <> Cells(i - 1, "G").Value Then
Rows(iTotal + 1).Insert
Cells(iTotal + 1, "J").Formula = _
"=SUMIF(J" & i & ":J" & iTotal & _
",""xxx"",I" & i & ":I" & iTotal & ")"
iTotal = i - 1
End If
Next i

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)