|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Is there a way to look at a column and if the cell begins with
01,02,03....98 and the cell color is either yellow or pink to sum those
just those cells?
So what I'd like to do is sum all the cells starting with 01 that the
cell background is yellow,
then sum all the cells starting with 01 that the cell background is
pink?
Any help would be greatly appreciated.
|
|
You will need VBA to count by colour.
See http://www.cpearson.com/excel/colors.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
<banderson[ at ]nwws.biz> wrote in message
news:1157548360.618301.128340[ at ]d34g2000cwd.googlegroups.com...
[Quoted Text] > Is there a way to look at a column and if the cell begins with
> 01,02,03....98 and the cell color is either yellow or pink to sum those
> just those cells?
>
> So what I'd like to do is sum all the cells starting with 01 that the
> cell background is yellow,
> then sum all the cells starting with 01 that the cell background is
> pink?
>
> Any help would be greatly appreciated.
>
|
|
Bernard,
I already have that part taken care of, but what I need to do is have
it only look at the cells that start with 01 in column A and have a
certain color and sum those.
Right now I'm doing a filter and using the begins with 01 copying to a
different sheet then running my VBA to get the sumbycolor.
I was just wondering if there was an easy way within VBA to do this all
in one motion
instead of the 20 steps I'm taking to do each one, considering I have
to go from
01 - 99.
Thanks Bernard.
|
|
This shouldn't be too difficult. It's difficult to give you exact code,
since I'm not sure where you want the information, but an outline as follows
should do the trick.
dim MySearch as string
dim MyRows as integer 'this will be the number of rows in your table
dim MySum as double
MyRows = 1000 ' or whatever
for s = 1 to 99
MySum = 0
MySearch = right("0" & i,2) 'this will give you the 01,02,03...
for i = 1 to MyRows
if left(cells(i,1),2) = MySearch then
if cells(i,1).indexcolor = whatever then
MySum = MySum + cells(i,2) 'or whatever you're summing
end if
end if
next i
cells(MyRows + s,whatever) = MySum 'save the final sum to whatever cell
you want
next s
I'm not sure if this is what you're after?!?
Regards
Glenton
glenton[ at ]leviqqio.com
www.leviqqio.com
"banderson[ at ]nwws.biz" wrote:
[Quoted Text] >
> Bernard,
> I already have that part taken care of, but what I need to do is have
> it only look at the cells that start with 01 in column A and have a
> certain color and sum those.
> Right now I'm doing a filter and using the begins with 01 copying to a
> different sheet then running my VBA to get the sumbycolor.
>
> I was just wondering if there was an easy way within VBA to do this all
> in one motion
> instead of the 20 steps I'm taking to do each one, considering I have
> to go from
> 01 - 99.
> Thanks Bernard.
>
>
|
|
Glenton thanks for the reply
I'm not really following what the "s" is suppose to be doing in this
code?
Also when you are using right in the code MySearch = Right("01" & i, 2)
you mean left correct.
Here is how the file is setup.
Column A has all the part#
01b412
01b123
01b512
*Note: not in order of first 2 characters so the 01's are all over the
place.
Then in Column J I have the $$ values I want to total based on the
first 2 characters
of column A and the background color of that cell, and only total the
01's with yellow & pink
backgrounds.
Thanks again!!
|
|
I got the following code to work for the seach within the column A
But the "if cell(i,1).indexcolor = 6"
Keeps giving me a Run-time Error "438"
Object does not support this property or method.
Am I using the wrong identifier with indexcolor?
I'm using Office XP Pro?
|
|
Hi
Try the following macro. You need to set the bits that have been marked
with '********* before you run it, and please save first. It will output a
table below your data, with 01,02,03... in the A column, the one color sum in
the B column, and the other color sum in the C column.
The parameters are:
MySh1 is the sheet where all this data is.
MyStartRow is the row number of the first entry of your database
MyEndRow is the row number of the last entry of your database
MyCheckCol is the column number with the data which starts with 01,02,...
MySumCol is the column number with the numbers to sum
MyColor(i) is the color index you want to sum over (can be increased from
just two colors if you want)
The three rows above the next c and next s just set up the table, and can be
adapted for however you want the data to be outputted.
Regards
Glenton
www.leviqqio.com
Sub MySum()
Dim MySum As Double
Dim MyColor(1)
Dim MySh1 As Worksheet
Set MySh1 = Sheets("Sheet2") '**************
MyStartRow = 3 '**************
MyEndRow = 18 '**************
MyCheckCol = 1 '**************
MySumCol = 10 '**************
MyColor(0) = 7 '**************
MyColor(1) = 6 '**************
For s = 1 To 99
MySearch = Right("0" & s, 2) 'this will give you the 01,02,03...
For c = 0 To 1
MySum = 0
For i = MyStartRow To MyEndRow
If Left(MySh1.Cells(i, MyCheckCol), 2) = MySearch Then
If MySh1.Cells(i, MyCheckCol).Interior.ColorIndex = MyColor(c) Then
MySum = MySum + MySh1.Cells(i, MySumCol) 'or whatever you're summing
End If
End If
Next i
MySh1.Cells(MyEndRow + s + 1, c + 2) = MySum 'save the final sum
MySh1.Cells(MyEndRow + s + 1, c + 2).Interior.ColorIndex = MyColor(c)
MySh1.Cells(MyEndRow + s + 1, 1) = "'" & MySearch
Next c
Next s
End Sub
"banderson[ at ]nwws.biz" wrote:
[Quoted Text] >
> Glenton thanks for the reply
>
> I'm not really following what the "s" is suppose to be doing in this
> code?
>
> Also when you are using right in the code MySearch = Right("01" & i, 2)
> you mean left correct.
>
> Here is how the file is setup.
> Column A has all the part#
> 01b412
> 01b123
> 01b512
> *Note: not in order of first 2 characters so the 01's are all over the
> place.
> Then in Column J I have the $$ values I want to total based on the
> first 2 characters
> of column A and the background color of that cell, and only total the
> 01's with yellow & pink
> backgrounds.
>
> Thanks again!!
>
>
|
|
Glenton,
I got the code to work properly.
Also you said you can add more than 2 colors,
could you elaborate on this for me?
Thanks again I really appreciate it!!
|
|
|