Mixing
Doing what COUNTIF does, but including merged cells
Yes, i know, merged cells are bad, but i need them for aesthethic reasons!
I have a bunch of cells with a label (a brief text) on them; i want to count how many times each labeled is present in a defined range. Doing this is easy with countif...but the aesthethic impact is awful. If i merge them all the appereance is good, but countif doesn't help anymore because it counts the whole cell as one.
I've been trying to figure out how to do what i want with a vba module, but i'm too bad for that.
This one gives me the number of cells from the first cell that has the string i search for.
Function dcounter(r As Range, s As String) As Integer
dcounter = 0
If Not r.Find(s) Is Nothing Then dcounter = r.Find(s).MergeArea.Cells.Count
End Function
I just need to figure out how to loop this for the whole range. I've been trying to play with For Each with no success. Any suggestion?
excel vba excel-vba
asked Jan 1 at 21:29
TakanTakan
1
|
show 2 more comments
Yes, i know, merged cells are bad, but i need them for aesthethic reasons!
I have a bunch of cells with a label (a brief text) on them; i want to count how many times each labeled is present in a defined range. Doing this is easy with countif...but the aesthethic impact is awful. If i merge them all the appereance is good, but countif doesn't help anymore because it counts the whole cell as one.
I've been trying to figure out how to do what i want with a vba module, but i'm too bad for that.
This one gives me the number of cells from the first cell that has the string i search for.
Function dcounter(r As Range, s As String) As Integer
dcounter = 0
If Not r.Find(s) Is Nothing Then dcounter = r.Find(s).MergeArea.Cells.Count
End Function
I just need to figure out how to loop this for the whole range. I've been trying to play with For Each with no success. Any suggestion?
excel vba excel-vba
asked Jan 1 at 21:29
TakanTakan
1
1
Could you post an image and/or better explain what is counted and what isn't?
– VBasic2008
Jan 1 at 21:40
FYI formatting as “centre across selection” gives the aesthetic of merged cells without any actual merging. In which case, countif works just fine.
– CallumDA
Jan 1 at 22:10
Please show us the formula that does not work on merged cells. BTW, why are merged cells bad?
– Gene
Jan 1 at 22:36
SinceCOUNTIFwill work on merged cells, it is hard to understand your problem. Please read the HELP topic for How to create a Minimal, Complete, and Verifiable example, and edit your question to show that.
– Ron Rosenfeld
Jan 2 at 1:11
I usually deal with the aesthethics by controlling grid lines and cell colouring. Adjoining cells coloured white will hide grid lines between them where as cells without colour show them. You can hide all grid lines in the sheet and apply borders of the default width and colour of grid lines where you want them.
– Variatus
Jan 2 at 5:04
|
show 2 more comments
Yes, i know, merged cells are bad, but i need them for aesthethic reasons!
I have a bunch of cells with a label (a brief text) on them; i want to count how many times each labeled is present in a defined range. Doing this is easy with countif...but the aesthethic impact is awful. If i merge them all the appereance is good, but countif doesn't help anymore because it counts the whole cell as one.
I've been trying to figure out how to do what i want with a vba module, but i'm too bad for that.
This one gives me the number of cells from the first cell that has the string i search for.
Function dcounter(r As Range, s As String) As Integer
dcounter = 0
If Not r.Find(s) Is Nothing Then dcounter = r.Find(s).MergeArea.Cells.Count
End Function
I just need to figure out how to loop this for the whole range. I've been trying to play with For Each with no success. Any suggestion?
excel vba excel-vba
asked Jan 1 at 21:29
TakanTakan
1
Yes, i know, merged cells are bad, but i need them for aesthethic reasons!
I have a bunch of cells with a label (a brief text) on them; i want to count how many times each labeled is present in a defined range. Doing this is easy with countif...but the aesthethic impact is awful. If i merge them all the appereance is good, but countif doesn't help anymore because it counts the whole cell as one.
I've been trying to figure out how to do what i want with a vba module, but i'm too bad for that.
This one gives me the number of cells from the first cell that has the string i search for.
Function dcounter(r As Range, s As String) As Integer
dcounter = 0
If Not r.Find(s) Is Nothing Then dcounter = r.Find(s).MergeArea.Cells.Count
End Function
I just need to figure out how to loop this for the whole range. I've been trying to play with For Each with no success. Any suggestion?
excel vba excel-vba
excel vba excel-vba
asked Jan 1 at 21:29
TakanTakan
1
asked Jan 1 at 21:29
TakanTakan
1
asked Jan 1 at 21:29
TakanTakan
1
asked Jan 1 at 21:29
TakanTakan
1
asked Jan 1 at 21:29
TakanTakan
1
1
1
Could you post an image and/or better explain what is counted and what isn't?
– VBasic2008
Jan 1 at 21:40
FYI formatting as “centre across selection” gives the aesthetic of merged cells without any actual merging. In which case, countif works just fine.
– CallumDA
Jan 1 at 22:10
Please show us the formula that does not work on merged cells. BTW, why are merged cells bad?
– Gene
Jan 1 at 22:36
SinceCOUNTIFwill work on merged cells, it is hard to understand your problem. Please read the HELP topic for How to create a Minimal, Complete, and Verifiable example, and edit your question to show that.
– Ron Rosenfeld
Jan 2 at 1:11
I usually deal with the aesthethics by controlling grid lines and cell colouring. Adjoining cells coloured white will hide grid lines between them where as cells without colour show them. You can hide all grid lines in the sheet and apply borders of the default width and colour of grid lines where you want them.
– Variatus
Jan 2 at 5:04
|
show 2 more comments
1
Could you post an image and/or better explain what is counted and what isn't?
– VBasic2008
Jan 1 at 21:40
FYI formatting as “centre across selection” gives the aesthetic of merged cells without any actual merging. In which case, countif works just fine.
– CallumDA
Jan 1 at 22:10
Please show us the formula that does not work on merged cells. BTW, why are merged cells bad?
– Gene
Jan 1 at 22:36
SinceCOUNTIFwill work on merged cells, it is hard to understand your problem. Please read the HELP topic for How to create a Minimal, Complete, and Verifiable example, and edit your question to show that.
– Ron Rosenfeld
Jan 2 at 1:11
I usually deal with the aesthethics by controlling grid lines and cell colouring. Adjoining cells coloured white will hide grid lines between them where as cells without colour show them. You can hide all grid lines in the sheet and apply borders of the default width and colour of grid lines where you want them.
– Variatus
Jan 2 at 5:04
1
1
Could you post an image and/or better explain what is counted and what isn't?
– VBasic2008
Jan 1 at 21:40
Could you post an image and/or better explain what is counted and what isn't?
– VBasic2008
Jan 1 at 21:40
FYI formatting as “centre across selection” gives the aesthetic of merged cells without any actual merging. In which case, countif works just fine.
– CallumDA
Jan 1 at 22:10
FYI formatting as “centre across selection” gives the aesthetic of merged cells without any actual merging. In which case, countif works just fine.
– CallumDA
Jan 1 at 22:10
Please show us the formula that does not work on merged cells. BTW, why are merged cells bad?
– Gene
Jan 1 at 22:36
Please show us the formula that does not work on merged cells. BTW, why are merged cells bad?
– Gene
Jan 1 at 22:36
Since
COUNTIF will work on merged cells, it is hard to understand your problem. Please read the HELP topic for How to create a Minimal, Complete, and Verifiable example, and edit your question to show that.– Ron Rosenfeld
Jan 2 at 1:11
Since
COUNTIF will work on merged cells, it is hard to understand your problem. Please read the HELP topic for How to create a Minimal, Complete, and Verifiable example, and edit your question to show that.– Ron Rosenfeld
Jan 2 at 1:11
I usually deal with the aesthethics by controlling grid lines and cell colouring. Adjoining cells coloured white will hide grid lines between them where as cells without colour show them. You can hide all grid lines in the sheet and apply borders of the default width and colour of grid lines where you want them.
– Variatus
Jan 2 at 5:04
I usually deal with the aesthethics by controlling grid lines and cell colouring. Adjoining cells coloured white will hide grid lines between them where as cells without colour show them. You can hide all grid lines in the sheet and apply borders of the default width and colour of grid lines where you want them.
– Variatus
Jan 2 at 5:04
|
show 2 more comments
2 Answers
2
active
oldest
votes
Something like this:
Function CountMerged(rng As Range, txt As String)
Dim col As Collection, n As Long, c
Set col = FindAll(rng, txt)
For Each c In col
n = n + c.MergeArea.Count
Next c
CountMerged = n
End Function
Public Function FindAll(rng As Range, val As String) As Collection
Dim rv As New Collection, f As Range
Dim addr As String
Debug.Print rng.Cells.Count
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not f Is Nothing Then addr = f.Address()
Do Until f Is Nothing
Debug.Print f.Address
rv.Add f
'Note: FindNext() won't work in a UDF
Set f = rng.Find(what:=val, after:=f)
If f.Address() = addr Then Exit Do
Loop
Set FindAll = rv
End Function
Note - merging/unmerging will not trigger a recalculation of the UDF, even if you add Application.Volatile It's not clear though from your question whether you're looking for a UDF...
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
add a comment |
Try this code instead:
Function dcounter(r As Range, s As String) As Integer
Dim c As Range
For Each c In r
If c.Value = s Then
dcounter = dcounter + c.MergeArea.Count
End If
Next
End Function
Reference: Range.Find method - For Each...Next
edited Jan 2 at 9:48
CallumDA
10.8k62242
answered Jan 2 at 5:02
onorioonorio
262
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999079%2fdoing-what-countif-does-but-including-merged-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Something like this:
Function CountMerged(rng As Range, txt As String)
Dim col As Collection, n As Long, c
Set col = FindAll(rng, txt)
For Each c In col
n = n + c.MergeArea.Count
Next c
CountMerged = n
End Function
Public Function FindAll(rng As Range, val As String) As Collection
Dim rv As New Collection, f As Range
Dim addr As String
Debug.Print rng.Cells.Count
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not f Is Nothing Then addr = f.Address()
Do Until f Is Nothing
Debug.Print f.Address
rv.Add f
'Note: FindNext() won't work in a UDF
Set f = rng.Find(what:=val, after:=f)
If f.Address() = addr Then Exit Do
Loop
Set FindAll = rv
End Function
Note - merging/unmerging will not trigger a recalculation of the UDF, even if you add Application.Volatile It's not clear though from your question whether you're looking for a UDF...
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
add a comment |
Something like this:
Function CountMerged(rng As Range, txt As String)
Dim col As Collection, n As Long, c
Set col = FindAll(rng, txt)
For Each c In col
n = n + c.MergeArea.Count
Next c
CountMerged = n
End Function
Public Function FindAll(rng As Range, val As String) As Collection
Dim rv As New Collection, f As Range
Dim addr As String
Debug.Print rng.Cells.Count
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not f Is Nothing Then addr = f.Address()
Do Until f Is Nothing
Debug.Print f.Address
rv.Add f
'Note: FindNext() won't work in a UDF
Set f = rng.Find(what:=val, after:=f)
If f.Address() = addr Then Exit Do
Loop
Set FindAll = rv
End Function
Note - merging/unmerging will not trigger a recalculation of the UDF, even if you add Application.Volatile It's not clear though from your question whether you're looking for a UDF...
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
add a comment |
Something like this:
Function CountMerged(rng As Range, txt As String)
Dim col As Collection, n As Long, c
Set col = FindAll(rng, txt)
For Each c In col
n = n + c.MergeArea.Count
Next c
CountMerged = n
End Function
Public Function FindAll(rng As Range, val As String) As Collection
Dim rv As New Collection, f As Range
Dim addr As String
Debug.Print rng.Cells.Count
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not f Is Nothing Then addr = f.Address()
Do Until f Is Nothing
Debug.Print f.Address
rv.Add f
'Note: FindNext() won't work in a UDF
Set f = rng.Find(what:=val, after:=f)
If f.Address() = addr Then Exit Do
Loop
Set FindAll = rv
End Function
Note - merging/unmerging will not trigger a recalculation of the UDF, even if you add Application.Volatile It's not clear though from your question whether you're looking for a UDF...
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
Something like this:
Function CountMerged(rng As Range, txt As String)
Dim col As Collection, n As Long, c
Set col = FindAll(rng, txt)
For Each c In col
n = n + c.MergeArea.Count
Next c
CountMerged = n
End Function
Public Function FindAll(rng As Range, val As String) As Collection
Dim rv As New Collection, f As Range
Dim addr As String
Debug.Print rng.Cells.Count
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not f Is Nothing Then addr = f.Address()
Do Until f Is Nothing
Debug.Print f.Address
rv.Add f
'Note: FindNext() won't work in a UDF
Set f = rng.Find(what:=val, after:=f)
If f.Address() = addr Then Exit Do
Loop
Set FindAll = rv
End Function
Note - merging/unmerging will not trigger a recalculation of the UDF, even if you add Application.Volatile It's not clear though from your question whether you're looking for a UDF...
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
answered Jan 1 at 23:24
Tim WilliamsTim Williams
88.5k97087
88.5k97087
add a comment |
add a comment |
Try this code instead:
Function dcounter(r As Range, s As String) As Integer
Dim c As Range
For Each c In r
If c.Value = s Then
dcounter = dcounter + c.MergeArea.Count
End If
Next
End Function
Reference: Range.Find method - For Each...Next
edited Jan 2 at 9:48
CallumDA
10.8k62242
answered Jan 2 at 5:02
onorioonorio
262
add a comment |
Try this code instead:
Function dcounter(r As Range, s As String) As Integer
Dim c As Range
For Each c In r
If c.Value = s Then
dcounter = dcounter + c.MergeArea.Count
End If
Next
End Function
Reference: Range.Find method - For Each...Next
edited Jan 2 at 9:48
CallumDA
10.8k62242
answered Jan 2 at 5:02
onorioonorio
262
add a comment |
Try this code instead:
Function dcounter(r As Range, s As String) As Integer
Dim c As Range
For Each c In r
If c.Value = s Then
dcounter = dcounter + c.MergeArea.Count
End If
Next
End Function
Reference: Range.Find method - For Each...Next
edited Jan 2 at 9:48
CallumDA
10.8k62242
answered Jan 2 at 5:02
onorioonorio
262
Try this code instead:
Function dcounter(r As Range, s As String) As Integer
Dim c As Range
For Each c In r
If c.Value = s Then
dcounter = dcounter + c.MergeArea.Count
End If
Next
End Function
Reference: Range.Find method - For Each...Next
edited Jan 2 at 9:48
CallumDA
10.8k62242
answered Jan 2 at 5:02
onorioonorio
262
edited Jan 2 at 9:48
CallumDA
10.8k62242
edited Jan 2 at 9:48
CallumDA
10.8k62242
edited Jan 2 at 9:48
CallumDA
10.8k62242
10.8k62242
answered Jan 2 at 5:02
onorioonorio
262
answered Jan 2 at 5:02
onorioonorio
262
answered Jan 2 at 5:02
onorioonorio
262
262
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999079%2fdoing-what-countif-does-but-including-merged-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Could you post an image and/or better explain what is counted and what isn't?
– VBasic2008
Jan 1 at 21:40
FYI formatting as “centre across selection” gives the aesthetic of merged cells without any actual merging. In which case, countif works just fine.
– CallumDA
Jan 1 at 22:10
Please show us the formula that does not work on merged cells. BTW, why are merged cells bad?
– Gene
Jan 1 at 22:36
Since
COUNTIFwill work on merged cells, it is hard to understand your problem. Please read the HELP topic for How to create a Minimal, Complete, and Verifiable example, and edit your question to show that.– Ron Rosenfeld
Jan 2 at 1:11
I usually deal with the aesthethics by controlling grid lines and cell colouring. Adjoining cells coloured white will hide grid lines between them where as cells without colour show them. You can hide all grid lines in the sheet and apply borders of the default width and colour of grid lines where you want them.
– Variatus
Jan 2 at 5:04