Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
I had this neat macro that counts how many cells are a certain color:
Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
Try this:
Public Function CountColors(ColorNbr As String) As Long Dim lCount As Long, c As Range lCount = 0 For Each c In Range("ThisRange") If c.Interior.color = ColorNbr Then If c.MergeCells = True Then lCount = lCount + c.Count Else lCount = lCount + 1 End If End If Next c CountColors = lCount End Function Sub TEST() MsgBox CountColors("825735") End Sub Hope this helps, Hutch "John" wrote: I had this neat macro that counts how many cells are a certain color: Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
Count the number of merged cells:
If cell.Interior.color = 825735 Then intCount = intCount + cell.MergeArea.Cells.Count cell.MergeArea.Cells.Count will return 1 for cells that are not merged.... HTH, Bernie MS Excel MVP "John" wrote in message ... I had this neat macro that counts how many cells are a certain color: Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
Hi Bernie,
The OP's question is highly ambiguous, but wouldn't adding cell.MergeArea.Cells.Count result in an overly excessive count, where multiple cells in a MergeArea are looped. (My guess is the intention of the question is the opposite of what it says, only count the first cell in the mergarea that matches the condition, but I may well be wrong!) Regards, Peter T "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Count the number of merged cells: If cell.Interior.color = 825735 Then intCount = intCount + cell.MergeArea.Cells.Count cell.MergeArea.Cells.Count will return 1 for cells that are not merged.... HTH, Bernie MS Excel MVP "John" wrote in message ... I had this neat macro that counts how many cells are a certain color: Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
Peter,
Only the OP knows for sure, but the question was: How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1 Which will give the wrong result if the selection covers the merged cell, but if the OP is stepping down a column, for example, then it would work. HTH, Bernie MS Excel MVP "Peter T" <peter_t@discussions wrote in message ... Hi Bernie, The OP's question is highly ambiguous, but wouldn't adding cell.MergeArea.Cells.Count result in an overly excessive count, where multiple cells in a MergeArea are looped. (My guess is the intention of the question is the opposite of what it says, only count the first cell in the mergarea that matches the condition, but I may well be wrong!) Regards, Peter T "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Count the number of merged cells: If cell.Interior.color = 825735 Then intCount = intCount + cell.MergeArea.Cells.Count cell.MergeArea.Cells.Count will return 1 for cells that are not merged.... HTH, Bernie MS Excel MVP "John" wrote in message ... I had this neat macro that counts how many cells are a certain color: Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
Indeed that's one (likely) interpretation of the question I didn't think
of., - and cells merged vertically in that column, oh I give up <g Regards, Peter T "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Peter, Only the OP knows for sure, but the question was: How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1 Which will give the wrong result if the selection covers the merged cell, but if the OP is stepping down a column, for example, then it would work. HTH, Bernie MS Excel MVP "Peter T" <peter_t@discussions wrote in message ... Hi Bernie, The OP's question is highly ambiguous, but wouldn't adding cell.MergeArea.Cells.Count result in an overly excessive count, where multiple cells in a MergeArea are looped. (My guess is the intention of the question is the opposite of what it says, only count the first cell in the mergarea that matches the condition, but I may well be wrong!) Regards, Peter T "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Count the number of merged cells: If cell.Interior.color = 825735 Then intCount = intCount + cell.MergeArea.Cells.Count cell.MergeArea.Cells.Count will return 1 for cells that are not merged.... HTH, Bernie MS Excel MVP "John" wrote in message ... I had this neat macro that counts how many cells are a certain color: Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count merged cells as if unmerged
I appreciate all the help. I decided to unmerge my cells, since I couldn't
take the risk of the cell count being inaccurate. "Peter T" <peter_t@discussions wrote in message ... Indeed that's one (likely) interpretation of the question I didn't think of., - and cells merged vertically in that column, oh I give up <g Regards, Peter T "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Peter, Only the OP knows for sure, but the question was: How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1 Which will give the wrong result if the selection covers the merged cell, but if the OP is stepping down a column, for example, then it would work. HTH, Bernie MS Excel MVP "Peter T" <peter_t@discussions wrote in message ... Hi Bernie, The OP's question is highly ambiguous, but wouldn't adding cell.MergeArea.Cells.Count result in an overly excessive count, where multiple cells in a MergeArea are looped. (My guess is the intention of the question is the opposite of what it says, only count the first cell in the mergarea that matches the condition, but I may well be wrong!) Regards, Peter T "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Count the number of merged cells: If cell.Interior.color = 825735 Then intCount = intCount + cell.MergeArea.Cells.Count cell.MergeArea.Cells.Count will return 1 for cells that are not merged.... HTH, Bernie MS Excel MVP "John" wrote in message ... I had this neat macro that counts how many cells are a certain color: Public Function CountColors(color As String) As Integer Dim intCount As Integer Dim cell As Range For Each cell In Range("ThisRange").Cells If cell.Interior.color = 825735 Then intCount = intCount + 1 Next cell CountColors = intCount End Function The problem is, I have merged some cells. How can I get a macro to count say, 3 cells merged into 1 as three cells, not 1, if they match a certain color?. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count merged cells | Excel Worksheet Functions | |||
Count merged cells | Excel Discussion (Misc queries) | |||
Count with merged cells | Excel Discussion (Misc queries) | |||
Creating Serial Additions of Numbers Formatted "M-N" In Merged and Unmerged Cells | Excel Worksheet Functions | |||
Retain value of merged cell in all unmerged cells | Excel Discussion (Misc queries) |