For Each-Next Construct Help Please...

Larry · Apr 15th, 2002, 03:17 PM

I am attempting to subtotal using a For Each Next Construct; however, I do not know which "element" to use to identify a change in my range.

The syntax is as follows:
For Each element In group
[Instructions]
[Exit For]
[Instructions]
Next [element]

I am wanting a subtotal row for each change in range A8:A65536, but the VBA does not recognize "Change" as a valid element.

Any help will be greatly appreciated.

Larry · Apr 16th, 2002, 12:30 AM

I aplogize if this is poor board etiquette, but I really need some help with this. So, TTT...

Tom Schreiner · Apr 16th, 2002, 01:46 AM

Hi Larry.
I am fairly new with Excel and will try to help you out, but I do not understand exactly what you are trying to do???
Tom

jrnyman · Apr 16th, 2002, 03:21 AM

Please post some sample (condensed) data, so we can give you a hand.

Larry · Apr 16th, 2002, 08:01 AM

Larry · Apr 16th, 2002, 08:08 AM

I am attempting to insert custom subtotals after each change in part number that is located in column A. For example:

Part# Qty
55012 15
55012 12
55012 1
55013 5
55013 8
55014 0

I am trying to use the For Each-Next Construct to say for each change in part number, insert a subtotal row. I have written the code for the subtotals, but they are acting as a grand total since they are going directly to the bottom of my data set.
I am using this For/With statement to insert the subtotals for each column I want totaled but I dont know what to use to get this at each change in part number.

With Sheets("Report").Cells(rowcount + 7, 5)
.Formula = "=Sum(E7:E" & rowcount + 6 & ")"
.Font.Bold = True
End With

Hopefully, this will help make my request a little clearer. I am very new to Excel Developing and I'm still unsure of the terminology.

_________________
Thanks,

Larry

[ This Message was edited by: Larry on 2002-04-16 07:09 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 03:17 PM	#1
Larry New Member Join Date: Apr 2002 Location: Plano, Texas Posts: 29	I am attempting to subtotal using a For Each Next Construct; however, I do not know which "element" to use to identify a change in my range. The syntax is as follows: For Each element In group [Instructions] [Exit For] [Instructions] Next [element] I am wanting a subtotal row for each change in range A8:A65536, but the VBA does not recognize "Change" as a valid element. Any help will be greatly appreciated.

Apr 16th, 2002, 12:30 AM	#2
Larry New Member Join Date: Apr 2002 Location: Plano, Texas Posts: 29	I aplogize if this is poor board etiquette, but I really need some help with this. So, TTT...

Apr 16th, 2002, 01:46 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Larry. I am fairly new with Excel and will try to help you out, but I do not understand exactly what you are trying to do??? Tom

Apr 16th, 2002, 03:21 AM	#4
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	Please post some sample (condensed) data, so we can give you a hand.

Apr 16th, 2002, 08:01 AM	#5
Larry New Member Join Date: Apr 2002 Location: Plano, Texas Posts: 29

Apr 16th, 2002, 08:08 AM	#6
Larry New Member Join Date: Apr 2002 Location: Plano, Texas Posts: 29	I am attempting to insert custom subtotals after each change in part number that is located in column A. For example: Part# Qty 55012 15 55012 12 55012 1 55013 5 55013 8 55014 0 I am trying to use the For Each-Next Construct to say for each change in part number, insert a subtotal row. I have written the code for the subtotals, but they are acting as a grand total since they are going directly to the bottom of my data set. I am using this For/With statement to insert the subtotals for each column I want totaled but I dont know what to use to get this at each change in part number. With Sheets("Report").Cells(rowcount + 7, 5) .Formula = "=Sum(E7:E" & rowcount + 6 & ")" .Font.Bold = True End With Hopefully, this will help make my request a little clearer. I am very new to Excel Developing and I'm still unsure of the terminology. _________________ Thanks, Larry [ This Message was edited by: Larry on 2002-04-16 07:09 ]