I have the following situation:

Column D
0H:8M
9H:39M
14H:46M ... and so on.

these cells DO NOT have any format. I wish to add the values before th
"H" and the values before the "M" and still have them separated by th
":".

for example...
Column D
0H:8M
9H:39M
----------
Total
9H:47M

To complicate things even more,... there are 3 different color cell
within column D. Below you'll see each color property (I don't know i
this could help).

Color.........R.....G.....B
Green.....204...255..204
Yellow....255...255..153
Red........255...128..128

What I want to accomplish is the following:
Select a range in column D of... let's say 200 rows and have code..
using something like an input box add total time for Green, Yellow an
Red cells... Therefore, adding 200 rows for example might yield a tota
green cells 450H:46M.

The following could be a beginning... for selecting range using a
input box.

'...Thanks Tom O
On error resume next
set rng = Application.InputBox( _
"Please select range with mouse", type:=8)
On Error goto 0
if not rng is nothing then

Also, I Found this on Chip Pearson's site...

Summing The Values Of Cells With A Specific Color

The following function will return the sum of cells in a range tha
have either an Interior (background) or Font of a specified color
InRange is the range of cells to examine, WhatColorIndex is th
ColorIndex value to count, and OfText indicates whether to return th
ColorIndex of the Font (if True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function
You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)


How do I integrate these codes together? can any1 help??
I realize that this is a daunting task.... but I would really b
gratefull beyong belief, if anyone can come up with a solution.

If any1 wishes... I coud email the workbook as well.


Larry
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Hi
you may try the following user defined function:

-----
Function sum_pseudo_time(rng As Range, Optional color_index As Integer)
As String
Dim cell As Range
Dim ret_str As String
Dim ret_value
Dim time_str As String

ret_value = 0
For Each cell In rng
If color_index Then
If cell.Value < "" And cell.Interior.ColorIndex = color_index
Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
Else
If cell.Value < "" Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
End If
Next
ret_str = CInt(ret_value * 24) & "H:" & (ret_value * 24 -
CInt(ret_value * 24)) * 60 & "M"

sum_pseudo_time = ret_str
End Function

-----

Call this function either without a color_index parameter:
=sum_pseudo_time(A1:A10)
this will return the sum of your cells regardless of their color

or use the color_index parameter (the functions used the color of the
interior/not the font color):
=sum_pseudo_time(A11:A13;3)
this will sum all cells this the color_index = 3 (this is red)
A table of color_indexes can be found on:
http://www.mvps.org/dmcritchie/excel/colors.htm




--
Regards
Frank Kabel
Frankfurt, Germany

I have the following situation:

Column D
0H:8M
9H:39M
14H:46M ... and so on.

these cells DO NOT have any format. I wish to add the values before
the "H" and the values before the "M" and still have them separated
by the ":".

for example...
Column D
0H:8M
9H:39M
----------
Total
9H:47M

To complicate things even more,... there are 3 different color cells
within column D. Below you'll see each color property (I don't know
if
this could help).

Color.........R.....G.....B
Green.....204...255..204
Yellow....255...255..153
Red........255...128..128

What I want to accomplish is the following:
Select a range in column D of... let's say 200 rows and have code...
using something like an input box add total time for Green, Yellow
and
Red cells... Therefore, adding 200 rows for example might yield a
total green cells 450H:46M.

The following could be a beginning... for selecting range using an
input box.

'...Thanks Tom O
On error resume next
set rng = Application.InputBox( _
"Please select range with mouse", type:=8)
On Error goto 0
if not rng is nothing then

Also, I Found this on Chip Pearson's site...

Summing The Values Of Cells With A Specific Color

The following function will return the sum of cells in a range that
have either an Interior (background) or Font of a specified color.
InRange is the range of cells to examine, WhatColorIndex is the
ColorIndex value to count, and OfText indicates whether to return the
ColorIndex of the Font (if True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function
You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)


How do I integrate these codes together? can any1 help??
I realize that this is a daunting task.... but I would really be
gratefull beyong belief, if anyone can come up with a solution.

If any1 wishes... I coud email the workbook as well.


Larry
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Hi
a small correction: change the line
ret_str = CInt(ret_value * 24) & "H:" & (ret_value * 24 -
CInt(ret_value * 24)) * 60 & "M"

to
ret_str = CInt(ret_value * 24) & "H:" & CInt((ret_value * 24 - _
CInt(ret_value * 24)) * 60) & "M"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel wrote:
Hi
you may try the following user defined function:

-----
Function sum_pseudo_time(rng As Range, Optional color_index As
Integer) As String
Dim cell As Range
Dim ret_str As String
Dim ret_value
Dim time_str As String

ret_value = 0
For Each cell In rng
If color_index Then
If cell.Value < "" And cell.Interior.ColorIndex =
color_index
Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
Else
If cell.Value < "" Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
End If
Next
ret_str = CInt(ret_value * 24) & "H:" & (ret_value * 24 -
CInt(ret_value * 24)) * 60 & "M"

sum_pseudo_time = ret_str
End Function

-----

Call this function either without a color_index parameter:
=sum_pseudo_time(A1:A10)
this will return the sum of your cells regardless of their color

or use the color_index parameter (the functions used the color of the
interior/not the font color):
=sum_pseudo_time(A11:A13;3)
this will sum all cells this the color_index = 3 (this is red)
A table of color_indexes can be found on:
http://www.mvps.org/dmcritchie/excel/colors.htm





I have the following situation:

Column D
0H:8M
9H:39M
14H:46M ... and so on.

these cells DO NOT have any format. I wish to add the values before
the "H" and the values before the "M" and still have them separated
by the ":".

for example...
Column D
0H:8M
9H:39M
----------
Total
9H:47M

To complicate things even more,... there are 3 different color cells
within column D. Below you'll see each color property (I don't know
if this could help).

Color.........R.....G.....B
Green.....204...255..204
Yellow....255...255..153
Red........255...128..128

What I want to accomplish is the following:
Select a range in column D of... let's say 200 rows and have code...
using something like an input box add total time for Green, Yellow
and Red cells... Therefore, adding 200 rows for example might yield
a
total green cells 450H:46M.

The following could be a beginning... for selecting range using an
input box.

'...Thanks Tom O
On error resume next
set rng = Application.InputBox( _
"Please select range with mouse", type:=8)
On Error goto 0
if not rng is nothing then

Also, I Found this on Chip Pearson's site...

Summing The Values Of Cells With A Specific Color

The following function will return the sum of cells in a range that
have either an Interior (background) or Font of a specified color.
InRange is the range of cells to examine, WhatColorIndex is the
ColorIndex value to count, and OfText indicates whether to return
the
ColorIndex of the Font (if True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function
You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)


How do I integrate these codes together? can any1 help??
I realize that this is a daunting task.... but I would really be
gratefull beyong belief, if anyone can come up with a solution.

If any1 wishes... I coud email the workbook as well.


Larry
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Thanks for responding Frank...

I'm having some problems with the code... (I applied your correction)

Function sum_pseudo_time(rng As Range, Optional color_index As Integer
'...getting a syntax error here
As String '....this line shows as red
Dim cell As Range
Dim ret_str As String
Dim ret_value
Dim time_str As String

ret_value = 0
For Each cell In rng
If color_index Then
If cell.Value < "" And cell.Interior.ColorIndex = color_index
'...this line shows as red
Then '...this line shows as red
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
Else
If cell.Value < "" Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
End If
Next
ret_str = CInt(ret_value * 24) & "H:" & CInt((ret_value * 24 - _
CInt(ret_value * 24)) * 60) & "M"

sum_pseudo_time = ret_str
End Function

-----

I tried invoking the code from the last cell in column D...

=sum_pseudo_time(D1:D5407)

I get one of those common errors "#NAME?"

another question: where do I place this code?
-in the sheet
-or in the modul

--
Message posted from http://www.ExcelForum.com

Hi
first the red lines: Both lines are only ONE line. So move the 'Then'
at the end of the first line (cause by your newsreaders linewrapping)

Try if this helps. You also may check the location of this function 8it
should go in a module of your workbook)


--
Regards
Frank Kabel
Frankfurt, Germany

Thanks for responding Frank...

I'm having some problems with the code... (I applied your correction)

Function sum_pseudo_time(rng As Range, Optional color_index As
Integer) '...getting a syntax error here
As String '....this line shows as red
Dim cell As Range
Dim ret_str As String
Dim ret_value
Dim time_str As String

ret_value = 0
For Each cell In rng
If color_index Then
If cell.Value < "" And cell.Interior.ColorIndex = color_index
'...this line shows as red
Then '...this line shows as red
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
Else
If cell.Value < "" Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
End If
Next
ret_str = CInt(ret_value * 24) & "H:" & CInt((ret_value * 24 - _
CInt(ret_value * 24)) * 60) & "M"

sum_pseudo_time = ret_str
End Function

-----

I tried invoking the code from the last cell in column D...

=sum_pseudo_time(D1:D5407)

I get one of those common errors "#NAME?"

another question: where do I place this code?
-in the sheet
-or in the module


---
Message posted from http://www.ExcelForum.com/

Thanks again Frank...
I selected a little range using the function with NO color index...


0H:3M
23H:28M
0H:8M
9H:39M
14H:46M
5H:56M
2H:54M
0H:1M
13H:51M
0H:1M
9H:46M
0H:13M
0H:4M
11H:54M
0H:13M
0H:15M
8H:41M
I used....
=sum_pseudo_time(D1:D17)
in cell D18
this is the result I got... "-" ???
102H:-7M

"-" ??? why the minus??

Thanks for all your help... you're really good.

- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Hi
replace the line
ret_str = CInt(ret_value * 24) & "H:" & CInt((ret_value * 24 - _
CInt(ret_value * 24)) * 60) & "M"

with the following lines
With Application.WorksheetFunction
ret_str = .RoundDown(ret_value * 24, 0) & "H:" & _
CInt((ret_value * 24 - .RoundDown(ret_value * 24, 0)) * 60) & "M"
End With


This returns
101H:53M

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks again Frank...
I selected a little range using the function with NO color index...


0H:3M
23H:28M
0H:8M
9H:39M
14H:46M
5H:56M
2H:54M
0H:1M
13H:51M
0H:1M
9H:46M
0H:13M
0H:4M
11H:54M
0H:13M
0H:15M
8H:41M
I used....
=sum_pseudo_time(D1:D17)
in cell D18
this is the result I got... "-" ???
102H:-7M

"-" ??? why the minus??

Thanks for all your help... you're really good.

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Thanks so much! :)

It's working!!

I don't know how to express my gratitute... may God bless you and make
you rich! Thanks!!


---
Message posted from http://www.ExcelForum.com/

Frank the code works really good. thanks so much!

I'm just curious if you can deal with a small x-tra challenge...

suppose I have the following column...

Column D

1D:20H:3M
20H:3M
0H:3M

Adding these should yield... 2D:16H:9M

how can the code you provided be tweaked to accomodate this example?

If you can solve this you're awesome
If you can't it's ok... the world moves on :) , you've helped me
great deal already.

- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Hi Larry
I knew befo give him a small finger and he wants the whole hand :-)

Use the following code (This will use the format D:H:H):

Function sum_pseudo_time(rng As Range, Optional color_index As Integer)
As String
Dim cell As Range
Dim ret_str As String
Dim ret_value
Dim time_str As String

ret_value = 0
For Each cell In rng
If color_index Then
If cell.Value < "" And cell.Interior.ColorIndex = color_index
Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
Else
If cell.Value < "" Then
time_str = Replace(Replace(Replace(cell.Value, "H", ""),
"M", ""), _
"D", "")
ret_value = ret_value + CDate(time_str)
End If
End If
Next
With Application.WorksheetFunction
ret_str = .RoundDown(ret_value, 0) & "D:" & _
.RoundDown((ret_value - .RoundDown(ret_value, 0)) * 24, 0) & _
"H:" & CInt((ret_value * 24 - .RoundDown(ret_value * 24, 0)) * 60) &
"M"
End With
sum_pseudo_time = ret_str
End Function


--
Regards
Frank Kabel
Frankfurt, Germany

Frank the code works really good. thanks so much!

I'm just curious if you can deal with a small x-tra challenge...

suppose I have the following column...

Column D

1D:20H:3M
20H:3M
0H:3M

Adding these should yield... 2D:16H:9M

how can the code you provided be tweaked to accomodate this example?

If you can solve this you're awesome
If you can't it's ok... the world moves on :) , you've helped me a
great deal already.

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Thanks Frank for being a good samaritan and for going the x-tra mile fo
me!

For helping me in your own time and without obligation. I still nee
some time to learn all this stuff (VBA) but it will come a time when
will begin to do things on my own and then help others. Until that tim
comes me and others rely on knowlegable individuals such as yourself t
show us the light.

About your statement:
"I knew befo give him a small finger and he wants the whole han
:-)"

That is exactly what I did... I was in the hole, and it took a helpin
"hand" to get me out :-).... a nail or a finger wouldn't have bee
enough to pull me out.... and for this I'm grateful.

Thanks!
- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Hi
thanks for your feedback and don't hesitate to ask more questions :-)

--
Regards
Frank Kabel
Frankfurt, Germany
"nrage21 " schrieb im
Newsbeitrag ...
Thanks Frank for being a good samaritan and for going the x-tra mile
for
me!

For helping me in your own time and without obligation. I still need
some time to learn all this stuff (VBA) but it will come a time when
I
will begin to do things on my own and then help others. Until that
time
comes me and others rely on knowlegable individuals such as yourself
to
show us the light.

About your statement:
"I knew befo give him a small finger and he wants the whole hand
:-)"

That is exactly what I did... I was in the hole, and it took a
helping
"hand" to get me out :-).... a nail or a finger wouldn't have been
enough to pull me out.... and for this I'm grateful.

Thanks!
- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/