Sum by color

[isay]

Book1
	A	B	C	D	E
1	Period Activity	Ending Actinvity	Total PA	Total EA
2	-----------------------	--------------------
3	$ 446,507.93	$ 446,507.93
4	$ 7,434.78	$ 7,434.78
5	$ (2,853,793.50)	$ (2,853,793.50)
6	$ -	$ -
7	$ 938.81	$ 938.81
8	$ 11.22	$ 11.22
9	$ -	$ -
10	$ -	$ -
11	$ 7,242.65	$ 7,242.65
12	$ 39,135.00	$ 39,135.00
13
14
15	$ -	$ -
16	$ -	$ -
17	$ -	$ -
18	$ -	$ -
19	$ -	$ -
20	$ -	$ -
21	$ -	$ -
22	$ -	$ -
23	$ -	$ -
24	$ -	$ -
25	$ -	$ -
26	$ -	$ -
27	$ -	$ -
Sheet1

Hi Firefytr...

I think my problem is a bit similar to ndbhatt. My code also retrieve a data from my source then inputted in my worksheet on the above format. I want to sum the amount of the Period Activity and Ending Activity by color in ColumG and ColumnH in Set A and Set B. I also dont know how many records will appear on my worksheet so I thought using:

cellrange = Activesheet.UsedRange.Columns(1).Address

for me to identify the used range in column A/B in set A/B then use the result address(cell range) in my sum function.

I have a function sumbycolor
=sumbycolor(cellrange,37,false)

to sum the amount of Period Activity and Ending Activity by color per set of my data.

But until now, I doesn't work. I am totally clueless on what are the next thing to do.

I really need your expertise. Thanks in advance.

 

[Zack Barresse]

Can you post exactly what it is that you have tried? Does the formula (SumByColor) work everywehre else? Can you get it to work at all? Where did you put it?

 

[isay]

Book2
	A	B	C	D	E	F	G	H
1	Center	Account	Account Description	Beginning Balance	Period Activity	Ending Balance	Sums	Sum (YTD)
2	----------	-----------	---------------------------------------------------	-----------------------	-----------------------	------------------------	-------------	----------------
3	CN-AGENTS
4	80149	420722	ggggg	$ 2,736,177.83	$ 63,637.66	$ 2,799,815.49
5		420723	ggggg	$ 131,839.13	$ 4,222.34	$ 136,061.47
6		420724	ggggg	$ 59,691,864.64	$ 3,504,745.22	$ 63,196,609.86
7		420733	ggggg	$ 7,305,831.34	$ 446,507.93	$ 7,752,339.27
8		440010	ggggg	$ -	$ (2,853,793.50)	$ (2,853,793.50)
9		441110	ggggg	$ (1,742.29)	$ 938.81	$ (803.48)
10		441120	ggggg	$ (70.72)	$ 11.22	$ (59.50)
11		441330	ggggg	$ (60,393,653.92)	$ (3,278,079.77)	$ (63,671,733.69)
12		448920	ggggg	$ 7,202.78	$ -	$ 7,202.78
13		461210	ggggg	$ 492,882.60	$ 7,242.65	$ 500,125.25
14		461230	ggggg	$ 5,578,850.28	$ 291,259.15	$ 5,870,109.43
15		461330	ggggg	$ 608,360.62	$ 61,991.18	$ 670,351.80
16		758141	ggggg	$ 734,830.00	$ 39,135.00	$ 773,965.00
17		758142	ggggg	$ 128,567.00	$ 7,220.00	$ 135,787.00
18		758143	ggggg	$ 2,110,043.85	$ 44,386.34	$ 2,154,430.19
19		758153	ggggg	$ 461,875.15	$ 4,500.00	$ 466,375.15
20
21
22	PRE-CEPA
23	80630	421101	bbbbb	$ (2,690,235.81)	$ -	$ (2,690,235.81)
24		421102	bbbbb	$ 1,691,853.64	$ -	$ 1,691,853.64
25		421103	bbbbb	$ 475,364.14	$ -	$ 475,364.14
26		421104	bbbbb	$ 2,719.00	$ -	$ 2,719.00
27		421106	bbbbb	$ (125,578.28)	$ -	$ (125,578.28)
28		421107	bbbbb	$ 103,801.74	$ -	$ 103,801.74
29		421108	bbbbb	$ 35,934.26	$ -	$ 35,934.26
30		421111	bbbbb	$ (10,090,513.90)	$ -	$ (10,090,513.90)
Sheet1

 

[isay]

my code is this for the above result.

Sub StatUpdate()
Dim FileName As String
Dim FileNum As Integer
Dim S As Long
Dim strline As String
Dim CurVar As String
Dim PrevVar As String
Dim intColorIndex As String
Dim intCounter6 As Integer
Dim intCounter7 As Integer
Dim intCounter8 As Integer
Dim intCounter9 As Integer
Dim intcounter10 As Integer
Dim intFreeFileNumber As Integer

intFreeFileNumber = FreeFile

intCounter6 = 0
intCounter7 = 0
intCounter8 = 0
intCounter9 = 0
intcounter10 = 0

Open "D:\TBStatYTD\sourcefile\TB-USD-EDITED.txt" For Input As #intFreeFileNumber

Do While Not EOF(intFreeFileNumber)

Line Input #intFreeFileNumber, strline


If CurVar = "" Then
CurVar = PrevVar
End If


If CurVar = "080149" Then

intCounter6 = intCounter6 + 1
If intCounter6 = 1 Then
ActiveSheet.Cells(S, 1) = "CN-AGENTS"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If

Select Case Trim(Mid(strline, 22, [10]))

'START EXPORT
Case "441110", "441120", "441130", "441210", "441220", "441230", "441330"
ActiveSheet.Cells(S, 2) = Mid(strline, 22, 10)
ActiveSheet.Cells(S, 3) = Mid(strline, 45, 31)
ActiveSheet.Cells(S, 4) = Mid(strline, 76, 20)
ActiveSheet.Cells(S, 5) = Mid(strline, 95, 19)
ActiveSheet.Cells(S, 5).Interior.ColorIndex = 36
ActiveSheet.Cells(S, 6) = Right(strline, 18)
ActiveSheet.Cells(S, 6).Interior.ColorIndex = 36
S = S + 1
intCountPlus = intCountPlus + 1

'END EXPORT
'START IMPORT
Case "758141", "758142", "758143", "758153"
ActiveSheet.Cells(S, 2) = Mid(strline, 22, 10)
ActiveSheet.Cells(S, 3) = Mid(strline, 45, 31)
ActiveSheet.Cells(S, 4) = Mid(strline, 76, 20)
ActiveSheet.Cells(S, 5) = Mid(strline, 95, 19)
ActiveSheet.Cells(S, 5).Interior.ColorIndex = 40
ActiveSheet.Cells(S, 6) = Right(strline, 18)
ActiveSheet.Cells(S, 6).Interior.ColorIndex = 40
S = S + 1
'END IMPORT
End Select

End If

If CurVar = "080630" Then
intCounter7 = intCounter7 + 1
If intCounter7 = 1 Then
ActiveSheet.Cells(S, 1) = "PRE-CEPA"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If

If CurVar = "080631" Then
intCounter8 = intCounter8 + 1
If intCounter8 = 1 Then
ActiveSheet.Cells(S, 1) = "CEPA-SOUTH"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If

If CurVar = "080639" Then
intCounter9 = intCounter9 + 1
If intCounter9 = 1 Then
ActiveSheet.Cells(S, 1) = "CEPA-CENTRAL"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If

If CurVar = "080640" Then
intcounter10 = intcounter10 + 1
If intcounter10 = 1 Then
ActiveSheet.Cells(S, 1) = "CEPA-NORTH"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If
PrevVar = CurVar
Loop

Close #1
End Sub

Sub AccountUpdate(tempStrLine As String, i As Long)

Select Case Trim(Mid(tempStrLine, 22, [10]))
Case "421101", "421106", "421111", "421201", "441210", "441220", "441230", "441330"
ActiveSheet.Cells(i, 2) = Mid(tempStrLine, 22, 10)
ActiveSheet.Cells(i, 3) = Mid(tempStrLine, 45, 31)
ActiveSheet.Cells(i, 4) = Mid(tempStrLine, 76, 20)
ActiveSheet.Cells(i, 5) = Mid(tempStrLine, 95, 19)
ActiveSheet.Cells(i, 5).Interior.ColorIndex = 36
ActiveSheet.Cells(i, 6) = Right(tempStrLine, 18)
ActiveSheet.Cells(i, 6).Interior.ColorIndex = 36
i = i + 1

Case "758041", "758042", "758043", "758052", "758053"
ActiveSheet.Cells(i, 2) = Mid(tempStrLine, 22, 10)
ActiveSheet.Cells(i, 3) = Mid(tempStrLine, 45, 31)
ActiveSheet.Cells(i, 4) = Mid(tempStrLine, 76, 20)
ActiveSheet.Cells(i, 5) = Mid(tempStrLine, 95, 19)
ActiveSheet.Cells(i, 5).Interior.ColorIndex = 40
ActiveSheet.Cells(i, 6) = Right(tempStrLine, 18)
ActiveSheet.Cells(i, 6).Interior.ColorIndex = 40
i = i + 1

End Select

End Sub

I am really sorry if my code is not code for I am still learning

Actually, I haven't use the the userange.address and sumfunction in my code because I am still on the testing process.

Need your expertise and advise.

 

[Zack Barresse]

Are you setting your Sum and Sum(YTD) via code? Are you wanting to? Or are you just looking to do this as a regular worksheet function entry?