VBA subtotal depending on indent

[jocker_boy]

Hi,

I have a table with text and numbers that i got from MSProject, for example:

[TABLE="width: 500"]
<tbody>[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]A
[/TD]
[TD="align: right"]36
[/TD]
[/TR]
[TR]
[TD] A.1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD] A.2[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD] A.3[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]61
[/TD]
[/TR]
[TR]
[TD] B.1
[/TD]
[TD="align: right"]38
[/TD]
[/TR]
[TR]
[TD] B.1.1[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD] B.1.2[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD] B.1.3[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD] B.2[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD] B.3[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]

My goal is to run a script that check the indent on column A:A, copy the values of column B:B to Column C:C, but insert subtotal formula for some lines depending on the indent, in this example are "A"; "B"; "B.1".

Is this possible?

Thanks,
Gonçalo

 

[Peter_SSs]

Is this possible?

Quite likely, but some more specific detail about exactly what you have, where and what result you want, where would help.

 

[jocker_boy]

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]A
[/TD]
[TD]7
[/TD]
[TD]=subtotal(9;B2:B4)
[/TD]
[/TR]
[TR]
[TD] A.1[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD] A.2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD] A.3
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]20
[/TD]
[TD]=subtotal(9;B6:B11)
[/TD]
[/TR]
[TR]
[TD] B.1
[/TD]
[TD]8
[/TD]
[TD]=subtotal(9;B7:B9)
[/TD]
[/TR]
[TR]
[TD] B.1.1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD] B.1.2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD] B.1.3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD] B.2
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD] B.3
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry, i forgot to show one table with the example.

I have already this table but it is all values, and the values in bold are values and not "sum" or "subtotal".
My goal is to replace all the bold values with subtotal(9;....) formula. Instead of replace, i don't mind to have a new column, for example column "C", like the example.

Thanks,
Gonçalo

 

[Peter_SSs]

Just checking a bit more ..

Before this process is started, does your column B look like you have shown in post 3 or does it look like my column B below, or something else?

By "indent" do you mean a physical indent in the cell like below (I have just used _ characters to force the look) or do you mean the suffixes like .1 or .1.1?

Does it have to be a SUBTOTAL formula or if another formula is possible to get the result could that be used?

Does it have to be done by vba or, if possible, a worksheet formula would do?

Excel Workbook
	A	B
1	A
2	_A.1	1
3	_A.2	2
4	_A.3	4
5	B
6	_B.1
7	__B.1.1	2
8	__B.1.2	3
9	__B.1.3	3
10	_B.2	7
11	_B.3	5
12
Subtotal