Sum by color

isay

New Member
Joined
Oct 14, 2005
Messages
39
Book1
ABCDE
1Period ActivityEnding ActinvityTotal PATotal 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. :oops:
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Oct 14, 2005
Messages
39
Book2
ABCDEFGH
1CenterAccountAccount DescriptionBeginning BalancePeriod ActivityEnding BalanceSumsSum (YTD)
2---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3CN-AGENTS
480149420722ggggg$ 2,736,177.83$ 63,637.66$ 2,799,815.49
5420723ggggg$ 131,839.13$ 4,222.34$ 136,061.47
6420724ggggg$ 59,691,864.64$ 3,504,745.22$ 63,196,609.86
7420733ggggg$ 7,305,831.34$ 446,507.93$ 7,752,339.27
8440010ggggg$ -$ (2,853,793.50)$ (2,853,793.50)
9441110ggggg$ (1,742.29)$ 938.81$ (803.48)
10441120ggggg$ (70.72)$ 11.22$ (59.50)
11441330ggggg$ (60,393,653.92)$ (3,278,079.77)$ (63,671,733.69)
12448920ggggg$ 7,202.78$ -$ 7,202.78
13461210ggggg$ 492,882.60$ 7,242.65$ 500,125.25
14461230ggggg$ 5,578,850.28$ 291,259.15$ 5,870,109.43
15461330ggggg$ 608,360.62$ 61,991.18$ 670,351.80
16758141ggggg$ 734,830.00$ 39,135.00$ 773,965.00
17758142ggggg$ 128,567.00$ 7,220.00$ 135,787.00
18758143ggggg$ 2,110,043.85$ 44,386.34$ 2,154,430.19
19758153ggggg$ 461,875.15$ 4,500.00$ 466,375.15
20
21
22PRE-CEPA
2380630421101bbbbb$ (2,690,235.81)$ -$ (2,690,235.81)
24421102bbbbb$ 1,691,853.64$ -$ 1,691,853.64
25421103bbbbb$ 475,364.14$ -$ 475,364.14
26421104bbbbb$ 2,719.00$ -$ 2,719.00
27421106bbbbb$ (125,578.28)$ -$ (125,578.28)
28421107bbbbb$ 103,801.74$ -$ 103,801.74
29421108bbbbb$ 35,934.26$ -$ 35,934.26
30421111bbbbb$ (10,090,513.90)$ -$ (10,090,513.90)
Sheet1
 

isay

New Member
Joined
Oct 14, 2005
Messages
39
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 :oops:

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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,134
Messages
5,576,292
Members
412,715
Latest member
amazingscan
Top