Excel Macro to sum rows based on the range prescribed using hyphen and comma's

[Lekha mohanty]

I have a excel sheet, where in B column I have range of information, In D column I want the sum of that range of that sheet, In A column I have the sheet name. and in each sheet I have information in C column. So for the first row the formula is =SUM(A!C1:C69)+SUM(A!C75:C76)+SUM(A!C80:C80), is it possible by any macro ?? Please help.

Book1ed.xlsx
	A	B	C	D
1	A	1-69,75-76,80	72	0
2	B	70-74,77-110	39
3	C	111-174,177-189,194-198,208-221	96
4	D	175-176,190-193,199-207,222-228,232	23
5	E	229-231,233-253	24
6	F	1-18,21,25-39	34
7	G	57-85	29
8	H	19-20,22-24,40-56,86-127,141-208	132
9	I	128-140	13
10	J	1-81	81
11	K	111,116-169	55
12	L	82-110,112-115	33
13	M	1-31,45-48,52-56,62-67,114-122,124,129-150,152,158,162-163,177-181,183-191,198-206,212-228	122
14	N	32-33,49-51,57-61,123,126-128,151,153-157,175-176,192-197,207-211	33
15	O	34-44	11
16	P	78,89-113,159-161,164-169,182,230-232,246-255	49
17	Q	68-77,79-88,125,170-174,229,233-245	40
18	R	1-13,52,54-62,86-99,123-125,127-130,135-136,142-147,218	53
19	S	77-85,100-122	32
20	T	63-76,126,131-134,137-141	24
21	U	14-50,158-164,166-167,169,174-75,177-194,207,213-217,219-225,233-241	89
22	V	51,53,148-157,165,168,170-173,176,195-206,208-212,226-232	43
Data

Cell Formulas
Range	Formula
D1	D1	=SUM(A!C1:C69)+SUM(A!C75:C76)+SUM(A!C80:C80)

 

[kennypete]

Something like this will work:

Sub subCreateColDformulas()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
Dim nLoop As Long
Dim sSheet As String
Dim sBstring As String
For nLoop = 1 To Split(rng.Address, "$")(4)
    sSheet = Cells(nLoop, 1)
    sBstring = "=sum(" & sSheet & "!C" & Cells(nLoop, 2) & ")"
    sBstring = Replace(sBstring, "-", ":C")
    sBstring = Replace(sBstring, ",", "," & sSheet & "!C")
    Range("D" & nLoop).Formula = sBstring
Next nLoop
End Sub

I tried it on a few sample sheets A, B and C with 1s, 2s and 3s in cols C of each and it did the job:

Book2
	A	B	C	D
1	A	1-69,75-76,80	72	72
2	B	70-74,77-110	39	78
3	C	111-174,177-189,194-198,208-221	96	288
Sheet1

Cell Formulas
Range	Formula
D1	D1	=SUM(A!C1:C69,A!C75:C76,A!C80)
D2	D2	=SUM(B!C70:C74,B!C77:C110)
D3	D3	=SUM('C'!C111:C174,'C'!C177:C189,'C'!C194:C198,'C'!C208:C221)

 

[Lekha mohanty]

Thank you