Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a variation in VBA for subtotals
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a variation in VBA for subtotals
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) "CJ" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with variation | Excel Discussion (Misc queries) | |||
Variation on If Then Else Theme | Excel Discussion (Misc queries) | |||
Variation from the mean? | Charts and Charting in Excel | |||
SUMIF variation? | Excel Worksheet Functions | |||
variation on countif? | Excel Worksheet Functions |