Count each group of values
 

I need a formula or vba code to count how many groups of "X" values and how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

JP

I'm assuming that the data is in the range A1 - AI1 inclusive (rather than in the range A1 - AH1 or all in one cell). If so, then this should help.

Tony

Sub bbb()
grpcnt = 0

Range("a1").Select
While Not IsEmpty(ActiveCell)
If ActiveCell = "X" Then
grpcnt = grpcnt + 1
thisgrp = 0
While ActiveCell = "X"
thisgrp = thisgrp + 1
ActiveCell.Offset(0, 1).Select
Wend
End If
If ActiveCell < "X" Then
Range("a1").Offset(grpcnt, 0).Value = grpcnt & " group " & thisgrp & " ""x"""
ActiveCell.Offset(0, 1).Select
End If
Wend

End Sub

Count each group of values
 

JP,

This counts the group in a selection. If more than one row is selected, an
x in the new row starts a new group no matter what.

Sub test2()

Dim r, x_range As Range
Dim x_counter, group_counter, i As Long
Dim msg As String

msg = ""

For Each r In Selection
If UCase(r) = "X" Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & "Group " & i & ":" & x_range.Areas(i).Cells.Count & " x" &
vbCrLf
Next i

MsgBox msg

End Sub
"JP" wrote in message
...
I need a formula or vba code to count how many groups of "X" values and
how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

woops, left in a couple of extra variables there, so for the sake of
neatness:

Dim r, x_range As Range
Dim i As Long
Dim msg As String

msg = ""

For Each r In Selection
If UCase(r) = "X" Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & "Group " & i & ":" & x_range.Areas(i).Cells.Count & " x" &
vbCrLf
Next i

MsgBox msg

End Sub

hth,

Doug

"JP" wrote in message
...
I need a formula or vba code to count how many groups of "X" values and
how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

Thank you very much for your help ACW.

Thank you very much "Doug Glancy", your solution is the best for my work.
Before this solution i need a lot of formulas to get the some results, now
is very simple with your code.

It's possible get the msg results in a range of cells? I need the values
number of counted "x" to calculate another things.
Sample: If a group of "x" = 7 then 7*another value

Thanks for your dedicated attention.I have excel experience but in vba i'm a
novice.
Excuse for my english.
Paulo Coelho





"Doug Glancy" wrote in message
...
woops, left in a couple of extra variables there, so for the sake of
neatness:

Dim r, x_range As Range
Dim i As Long
Dim msg As String

msg = ""

For Each r In Selection
If UCase(r) = "X" Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & "Group " & i & ":" & x_range.Areas(i).Cells.Count & " x" &
vbCrLf
Next i

MsgBox msg

End Sub

hth,

Doug

"JP" wrote in message
...
I need a formula or vba code to count how many groups of "X" values and
how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

JP,

Here's one that puts the results in rows in the Column A below any other
data. If you need something else, let me know. I changed a couple of other
things too:

Sub test()
Dim r, x_range, result_range As Range
Dim i As Long
Dim msg As String

msg = ""
For Each r In Selection
If UCase(r) = "X" Then
If IsEmpty(x_range) Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

If Not IsEmpty(x_range) Then
Set result_range = ActiveSheet.Range("A" & ActiveSheet.Range("A" &
Rows.Count).End(xlUp).Row + 3)
With result_range
.Cells(0, 1) = "Group"
.Cells(0, 2) = "X count"
For i = 1 To x_range.Areas.Count
.Cells(i, 1) = i
.Cells(i, 2) = x_range.Areas(i).Cells.Count
Next i
End With
Else
MsgBox "No Xs in selected area"
End If

End Sub

hth,

Doug
"JP" wrote in message
...
Thank you very much for your help ACW.

Thank you very much "Doug Glancy", your solution is the best for my work.
Before this solution i need a lot of formulas to get the some results, now
is very simple with your code.

It's possible get the msg results in a range of cells? I need the values
number of counted "x" to calculate another things.
Sample: If a group of "x" = 7 then 7*another value

Thanks for your dedicated attention.I have excel experience but in vba i'm
a
novice.
Excuse for my english.
Paulo Coelho





"Doug Glancy" wrote in message
...
woops, left in a couple of extra variables there, so for the sake of
neatness:

Dim r, x_range As Range
Dim i As Long
Dim msg As String

msg = ""

For Each r In Selection
If UCase(r) = "X" Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & "Group " & i & ":" & x_range.Areas(i).Cells.Count & " x"
&
vbCrLf
Next i

MsgBox msg

End Sub

hth,

Doug

"JP" wrote in message
...
I need a formula or vba code to count how many groups of "X" values
and
how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

Hello Doug

Thanks for your colaboration.
Another help please:

It´s possible fix the results in a range of cells. I need the values always
in some cells to make calculations.
Sample:A1:A7 groups; B1:B7 x counts and C1:C7 where y put my needed
calculations to be used by entire Worksheet functions
The ranges are flexible, another ranges be Ok.
A B C
Group X count Hours
1 5 30
2 4 24
3 10 48
4 2 12
5 3 18
6 4 24


Im a Health worker and this Worksheet is intended to count the lacks of my
personal group of workers in the hospital.
1 "x" = 1/5 of the week of work(30 hours) = 6
2 "x" = 2/5 .... =12
3 "x" = 3/5 .... =18
4 "x" = 4/5 .... =24
5, 6 or 7 "x" = 5/5 .... =30

Thanks for your attention
Best Regards
Paulo Coelho




"Doug Glancy" wrote in message
...
JP,

Here's one that puts the results in rows in the Column A below any other
data. If you need something else, let me know. I changed a couple of
other
things too:

Sub test()
Dim r, x_range, result_range As Range
Dim i As Long
Dim msg As String

msg = ""
For Each r In Selection
If UCase(r) = "X" Then
If IsEmpty(x_range) Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

If Not IsEmpty(x_range) Then
Set result_range = ActiveSheet.Range("A" & ActiveSheet.Range("A" &
Rows.Count).End(xlUp).Row + 3)
With result_range
.Cells(0, 1) = "Group"
.Cells(0, 2) = "X count"
For i = 1 To x_range.Areas.Count
.Cells(i, 1) = i
.Cells(i, 2) = x_range.Areas(i).Cells.Count
Next i
End With
Else
MsgBox "No Xs in selected area"
End If

End Sub

hth,

Doug
"JP" wrote in message
...
Thank you very much for your help ACW.

Thank you very much "Doug Glancy", your solution is the best for my
work.
Before this solution i need a lot of formulas to get the some results,
now
is very simple with your code.

It's possible get the msg results in a range of cells? I need the
values
number of counted "x" to calculate another things.
Sample: If a group of "x" = 7 then 7*another value

Thanks for your dedicated attention.I have excel experience but in vba
i'm
a
novice.
Excuse for my english.
Paulo Coelho





"Doug Glancy" wrote in message
...
woops, left in a couple of extra variables there, so for the sake of
neatness:

Dim r, x_range As Range
Dim i As Long
Dim msg As String

msg = ""

For Each r In Selection
If UCase(r) = "X" Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & "Group " & i & ":" & x_range.Areas(i).Cells.Count & "
x"
&
vbCrLf
Next i

MsgBox msg

End Sub

hth,

Doug

"JP" wrote in message
...
I need a formula or vba code to count how many groups of "X" values
and
how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X X
X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

JP,

I think this does what you want. Let me know:

Sub test()

Dim r, x_range, result_range As Range
Dim i As Long
Dim msg As String

msg = ""
For Each r In Selection
If UCase(r) = "X" Then
If IsEmpty(x_range) Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

If Not IsEmpty(x_range) Then
Set result_range = ActiveSheet.Range("A2")
With result_range
.Cells(0, 1) = "Group"
.Cells(0, 2) = "X count"
.Cells(0, 3) = "Hours"
For i = 1 To x_range.Areas.Count
.Cells(i, 1) = i
.Cells(i, 2) = x_range.Areas(i).Cells.Count
.Cells(i, 3).Formula = "=B" & Rows(i).Row + 1 & "*6"
Next i
End With
Else
MsgBox "No Xs in selected area"
End If

End Sub

Doug

"JP" wrote in message
...
Hello Doug

Thanks for your colaboration.
Another help please:

It´s possible fix the results in a range of cells. I need the values
always
in some cells to make calculations.
Sample:A1:A7 groups; B1:B7 x counts and C1:C7 where y put my needed
calculations to be used by entire Worksheet functions
The ranges are flexible, another ranges be Ok.
A B C
Group X count Hours
1 5 30
2 4 24
3 10 48
4 2 12
5 3 18
6 4 24


Im a Health worker and this Worksheet is intended to count the lacks of my
personal group of workers in the hospital.
1 "x" = 1/5 of the week of work(30 hours) = 6
2 "x" = 2/5 .... =12
3 "x" = 3/5 .... =18
4 "x" = 4/5 .... =24
5, 6 or 7 "x" = 5/5 .... =30

Thanks for your attention
Best Regards
Paulo Coelho




"Doug Glancy" wrote in message
...
JP,

Here's one that puts the results in rows in the Column A below any other
data. If you need something else, let me know. I changed a couple of
other
things too:

Sub test()
Dim r, x_range, result_range As Range
Dim i As Long
Dim msg As String

msg = ""
For Each r In Selection
If UCase(r) = "X" Then
If IsEmpty(x_range) Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

If Not IsEmpty(x_range) Then
Set result_range = ActiveSheet.Range("A" & ActiveSheet.Range("A" &
Rows.Count).End(xlUp).Row + 3)
With result_range
.Cells(0, 1) = "Group"
.Cells(0, 2) = "X count"
For i = 1 To x_range.Areas.Count
.Cells(i, 1) = i
.Cells(i, 2) = x_range.Areas(i).Cells.Count
Next i
End With
Else
MsgBox "No Xs in selected area"
End If

End Sub

hth,

Doug
"JP" wrote in message
...
Thank you very much for your help ACW.

Thank you very much "Doug Glancy", your solution is the best for my
work.
Before this solution i need a lot of formulas to get the some results,
now
is very simple with your code.

It's possible get the msg results in a range of cells? I need the
values
number of counted "x" to calculate another things.
Sample: If a group of "x" = 7 then 7*another value

Thanks for your dedicated attention.I have excel experience but in vba
i'm
a
novice.
Excuse for my english.
Paulo Coelho





"Doug Glancy" wrote in message
...
woops, left in a couple of extra variables there, so for the sake of
neatness:

Dim r, x_range As Range
Dim i As Long
Dim msg As String

msg = ""

For Each r In Selection
If UCase(r) = "X" Then
If x_range Is Nothing Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

For i = 1 To x_range.Areas.Count
msg = msg & "Group " & i & ":" & x_range.Areas(i).Cells.Count &
"
x"
&
vbCrLf
Next i

MsgBox msg

End Sub

hth,

Doug

"JP" wrote in message
...
I need a formula or vba code to count how many groups of "X"
values
and
how
many "X" in each group.
Sample:

a1
ah1
X X X X X X X A A A A X X X X X X B B B B B B X X X X X X X X X X
X
X

Result i need with the code or formula
1 group 7 "x"
2 group 6 "x"
3 group 12"x"

Thanks for help
Paulo

Count each group of values
 

You are a friend Doug Glancy.

This works exactly how I want it to. thanks for the time you lost to help
me.
When i finish my hard work with this project i send you a copy.
Now i´m going work.
If you need something i´m here in Portugal

Thanks
Paulo Coelho








"Doug Glancy" wrote in message
...
JP,

I think this does what you want. Let me know:

Sub test()

Dim r, x_range, result_range As Range
Dim i As Long
Dim msg As String

msg = ""
For Each r In Selection
If UCase(r) = "X" Then
If IsEmpty(x_range) Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

If Not IsEmpty(x_range) Then
Set result_range = ActiveSheet.Range("A2")
With result_range
.Cells(0, 1) = "Group"
.Cells(0, 2) = "X count"
.Cells(0, 3) = "Hours"
For i = 1 To x_range.Areas.Count
.Cells(i, 1) = i
.Cells(i, 2) = x_range.Areas(i).Cells.Count
.Cells(i, 3).Formula = "=B" & Rows(i).Row + 1 & "*6"
Next i
End With
Else
MsgBox "No Xs in selected area"
End If

End Sub

Doug

Count each group of values
 

Paulo,

You are very welcome.

Doug

"JP" wrote in message
...
You are a friend Doug Glancy.

This works exactly how I want it to. thanks for the time you lost to help
me.
When i finish my hard work with this project i send you a copy.
Now i´m going work.
If you need something i´m here in Portugal

Thanks
Paulo Coelho








"Doug Glancy" wrote in message
...
JP,

I think this does what you want. Let me know:

Sub test()

Dim r, x_range, result_range As Range
Dim i As Long
Dim msg As String

msg = ""
For Each r In Selection
If UCase(r) = "X" Then
If IsEmpty(x_range) Then
Set x_range = r
Else
Set x_range = Union(x_range, r)
End If
End If
Next r

If Not IsEmpty(x_range) Then
Set result_range = ActiveSheet.Range("A2")
With result_range
.Cells(0, 1) = "Group"
.Cells(0, 2) = "X count"
.Cells(0, 3) = "Hours"
For i = 1 To x_range.Areas.Count
.Cells(i, 1) = i
.Cells(i, 2) = x_range.Areas(i).Cells.Count
.Cells(i, 3).Formula = "=B" & Rows(i).Row + 1 & "*6"
Next i
End With
Else
MsgBox "No Xs in selected area"
End If

End Sub

Doug