Hi,
I have a multiple worksheets in a workbook each refers to one week of
year. Each sheets there is a one week schedule of staff. I have a
combobox1,command button1and listbox1 in a userform.After you select
the name of staff from combobox1, once I click on commandbutton1 I
want to display the name of the sheet in the first column of list box
and for the second column I want total number of "S" shift of that
staff. I wrote the code as follows which I get the sheet names(it
means the staff rostered for that week) but could get the countif
value. Coz,countif function needs to look for whole row as a range. My
question how can a look for a range for each sheet if the range is
entire row,or in row 202 from column 1 to 15(thats my range).For a
referance i write my code below:
Sub TotalShift()
Dim cell As Range
Dim trddate As Variant
Dim xcell As Range
Dim trd As Variant
Dim Sht As Worksheet
trddate = ComboBox1.Text
For Each Sht In ThisWorkbook.Sheets
Set xcell = Sht.Range("A1:AQ1000")
For Each cell In xcell.Columns(1).Cells
If cell.Text = trddate Then
UserForm1.ListBox1.AddItem Sht.Name
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
1) = _
Application.WorksheetFunction.CountIf(cell.Row, "S")
' !!!! here how to get the range for each sheet
End
If
' certain row
Next cell
Next Sht
End Sub

On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> Hi,
> I have a multiple worksheets in a workbook each refers to one week of
> year. Each sheets there is a one week schedule of staff. I have a
> combobox1,command button1and listbox1 in a userform.After you select
> the name of staff from combobox1, once I click on commandbutton1 I
> want to display the name of the sheet in the first column of list box
> and for the second column I want total number of "S" shift of that
> staff. I wrote the code as follows which I get the sheet names(it
> means the staff rostered for that week) but could get the countif
> value. Coz,countif function needs to look for whole row as a range. My
> question how can a look for a range for each sheet if the range is
> entire row,or in row 202 from column 1 to 15(thats my range).For a
> referance i write my code below:
> Sub TotalShift()
> Dim cell As Range
> Dim trddate As Variant
> Dim xcell As Range
> Dim trd As Variant
> Dim Sht As Worksheet
> trddate = ComboBox1.Text
> For Each Sht In ThisWorkbook.Sheets
> Set xcell = Sht.Range("A1:AQ1000")
> For Each cell In xcell.Columns(1).Cells
> If cell.Text = trddate Then
> UserForm1.ListBox1.AddItem Sht.Name
> UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> 1) = _
> Application.WorksheetFunction.CountIf(cell.Row, "S")
> ' !!!! here how to get the range for each sheet
> End
> If
> ' certain row
> Next cell
> Next Sht
> End Sub

Hi,
sorry after I post above query I found out the answer by myself.All I
need to use
Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
but still I get one more question, how can I eliminate the values if
the column is hidden.Is there any way to deduct the hidden column
values
Thanks for the help

If Cell.EntireColumn.Hidden = True Then
'did you mean row?
'If Cell.EntireRow.Hidden = True Then
'skip it
Else
'do the work
End If

"(E-Mail Removed)" wrote:
>
> On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> > Hi,
> > I have a multiple worksheets in a workbook each refers to one week of
> > year. Each sheets there is a one week schedule of staff. I have a
> > combobox1,command button1and listbox1 in a userform.After you select
> > the name of staff from combobox1, once I click on commandbutton1 I
> > want to display the name of the sheet in the first column of list box
> > and for the second column I want total number of "S" shift of that
> > staff. I wrote the code as follows which I get the sheet names(it
> > means the staff rostered for that week) but could get the countif
> > value. Coz,countif function needs to look for whole row as a range. My
> > question how can a look for a range for each sheet if the range is
> > entire row,or in row 202 from column 1 to 15(thats my range).For a
> > referance i write my code below:
> > Sub TotalShift()
> > Dim cell As Range
> > Dim trddate As Variant
> > Dim xcell As Range
> > Dim trd As Variant
> > Dim Sht As Worksheet
> > trddate = ComboBox1.Text
> > For Each Sht In ThisWorkbook.Sheets
> > Set xcell = Sht.Range("A1:AQ1000")
> > For Each cell In xcell.Columns(1).Cells
> > If cell.Text = trddate Then
> > UserForm1.ListBox1.AddItem Sht.Name
> > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > 1) = _
> > Application.WorksheetFunction.CountIf(cell.Row, "S")
> > ' !!!! here how to get the range for each sheet
> > End
> > If
> > ' certain row
> > Next cell
> > Next Sht
> > End Sub
>
> Hi,
> sorry after I post above query I found out the answer by myself.All I
> need to use
> Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> but still I get one more question, how can I eliminate the values if
> the column is hidden.Is there any way to deduct the hidden column
> values
> Thanks for the help

--

Dave Peterson

On Feb 17, 4:00 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> If Cell.EntireColumn.Hidden = True Then
> 'did you mean row?
> 'If Cell.EntireRow.Hidden = True Then
> 'skip it
> Else
> 'do the work
> End If
>
>
>
> "bah...@gmail.com" wrote:
>
> > On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> > > Hi,
> > > I have a multiple worksheets in a workbook each refers to one week of
> > > year. Each sheets there is a one week schedule of staff. I have a
> > > combobox1,command button1and listbox1 in a userform.After you select
> > > the name of staff from combobox1, once I click on commandbutton1 I
> > > want to display the name of the sheet in the first column of list box
> > > and for the second column I want total number of "S" shift of that
> > > staff. I wrote the code as follows which I get the sheet names(it
> > > means the staff rostered for that week) but could get the countif
> > > value. Coz,countif function needs to look for whole row as a range. My
> > > question how can a look for a range for each sheet if the range is
> > > entire row,or in row 202 from column 1 to 15(thats my range).For a
> > > referance i write my code below:
> > > Sub TotalShift()
> > > Dim cell As Range
> > > Dim trddate As Variant
> > > Dim xcell As Range
> > > Dim trd As Variant
> > > Dim Sht As Worksheet
> > > trddate = ComboBox1.Text
> > > For Each Sht In ThisWorkbook.Sheets
> > > Set xcell = Sht.Range("A1:AQ1000")
> > > For Each cell In xcell.Columns(1).Cells
> > > If cell.Text = trddate Then
> > > UserForm1.ListBox1.AddItem Sht.Name
> > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > 1) = _
> > > Application.WorksheetFunction.CountIf(cell.Row, "S")
> > > ' !!!! here how to get the range for each sheet
> > > End
> > > If
> > > ' certain row
> > > Next cell
> > > Next Sht
> > > End Sub
>
> > Hi,
> > sorry after I post above query I found out the answer by myself.All I
> > need to use
> > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > but still I get one more question, how can I eliminate the values if
> > the column is hidden.Is there any way to deduct the hidden column
> > values
> > Thanks for the help
>
> --
>
> Dave Peterson

Thanks Dave but this is not quite I want. Lets say in row number 22 I
want to check below
x=Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
but if in that sheet if one column hidden and has "S" value, in that
case x value will be extra one(actually you might ask why I don`t
simply delete that column?
I cannot do that,coz there are too many sheets and on each sheets
there is always different column is hidden.I am not the one who create
that worksheet actually.I just took it from my colleague)
Is that possible to skip the value if the column is hidden for that
row.\

For Each Sht In ThisWorkbook.Sheets
Set xcell = Sht.Range("A1:AQ1000")
For Each cell In xcell.Columns(1).Cells
If cell.Text = trddate Then
UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
1).Value

UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
1) = _
Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row),
"S") ' here I want to eliminate in that cell.row if the
column is hidden and


' the column has "S" value


thanks for your help

Dim cell As Range
Dim vRng As Range
Dim vArea As Range
Dim myCount As Long

Set cell = ActiveCell
Set vRng = Nothing
On Error Resume Next
Set vRng = cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

myCount = 0
If vRng Is Nothing Then
'no visible cells
Else
For Each vArea In vRng.Areas
myCount = myCount + Application.CountIf(vArea, "X")
Next vArea
End If

with UserForm1.ListBox1
.List(.ListCount - 1, 1) = myCount
End with

ps.
This kind of thing:
Sht.Rows(cell.Row)
can be written as:
cell.entirerow
(As long as cell is on sht)





"(E-Mail Removed)" wrote:
>
> On Feb 17, 4:00 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > If Cell.EntireColumn.Hidden = True Then
> > 'did you mean row?
> > 'If Cell.EntireRow.Hidden = True Then
> > 'skip it
> > Else
> > 'do the work
> > End If
> >
> >
> >
> > "bah...@gmail.com" wrote:
> >
> > > On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> > > > Hi,
> > > > I have a multiple worksheets in a workbook each refers to one week of
> > > > year. Each sheets there is a one week schedule of staff. I have a
> > > > combobox1,command button1and listbox1 in a userform.After you select
> > > > the name of staff from combobox1, once I click on commandbutton1 I
> > > > want to display the name of the sheet in the first column of list box
> > > > and for the second column I want total number of "S" shift of that
> > > > staff. I wrote the code as follows which I get the sheet names(it
> > > > means the staff rostered for that week) but could get the countif
> > > > value. Coz,countif function needs to look for whole row as a range. My
> > > > question how can a look for a range for each sheet if the range is
> > > > entire row,or in row 202 from column 1 to 15(thats my range).For a
> > > > referance i write my code below:
> > > > Sub TotalShift()
> > > > Dim cell As Range
> > > > Dim trddate As Variant
> > > > Dim xcell As Range
> > > > Dim trd As Variant
> > > > Dim Sht As Worksheet
> > > > trddate = ComboBox1.Text
> > > > For Each Sht In ThisWorkbook.Sheets
> > > > Set xcell = Sht.Range("A1:AQ1000")
> > > > For Each cell In xcell.Columns(1).Cells
> > > > If cell.Text = trddate Then
> > > > UserForm1.ListBox1.AddItem Sht.Name
> > > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > > 1) = _
> > > > Application.WorksheetFunction.CountIf(cell.Row, "S")
> > > > ' !!!! here how to get the range for each sheet
> > > > End
> > > > If
> > > > ' certain row
> > > > Next cell
> > > > Next Sht
> > > > End Sub
> >
> > > Hi,
> > > sorry after I post above query I found out the answer by myself.All I
> > > need to use
> > > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > > but still I get one more question, how can I eliminate the values if
> > > the column is hidden.Is there any way to deduct the hidden column
> > > values
> > > Thanks for the help
> >
> > --
> >
> > Dave Peterson
>
> Thanks Dave but this is not quite I want. Lets say in row number 22 I
> want to check below
> x=Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> but if in that sheet if one column hidden and has "S" value, in that
> case x value will be extra one(actually you might ask why I don`t
> simply delete that column?
> I cannot do that,coz there are too many sheets and on each sheets
> there is always different column is hidden.I am not the one who create
> that worksheet actually.I just took it from my colleague)
> Is that possible to skip the value if the column is hidden for that
> row.\
>
> For Each Sht In ThisWorkbook.Sheets
> Set xcell = Sht.Range("A1:AQ1000")
> For Each cell In xcell.Columns(1).Cells
> If cell.Text = trddate Then
> UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
> 1).Value
>
> UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> 1) = _
> Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row),
> "S") ' here I want to eliminate in that cell.row if the
> column is hidden and
>
>
> ' the column has "S" value
>
> thanks for your help

--

Dave Peterson

I used the activecell for testing.

You'd remove that line when you put this code into your loop.

Dave Peterson wrote:
>
> Dim cell As Range
> Dim vRng As Range
> Dim vArea As Range
> Dim myCount As Long
>
> Set cell = ActiveCell
> Set vRng = Nothing
> On Error Resume Next
> Set vRng = cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
> On Error GoTo 0
>
> myCount = 0
> If vRng Is Nothing Then
> 'no visible cells
> Else
> For Each vArea In vRng.Areas
> myCount = myCount + Application.CountIf(vArea, "X")
> Next vArea
> End If
>
> with UserForm1.ListBox1
> .List(.ListCount - 1, 1) = myCount
> End with
>
> ps.
> This kind of thing:
> Sht.Rows(cell.Row)
> can be written as:
> cell.entirerow
> (As long as cell is on sht)
>
> "(E-Mail Removed)" wrote:
> >
> > On Feb 17, 4:00 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > If Cell.EntireColumn.Hidden = True Then
> > > 'did you mean row?
> > > 'If Cell.EntireRow.Hidden = True Then
> > > 'skip it
> > > Else
> > > 'do the work
> > > End If
> > >
> > >
> > >
> > > "bah...@gmail.com" wrote:
> > >
> > > > On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> > > > > Hi,
> > > > > I have a multiple worksheets in a workbook each refers to one week of
> > > > > year. Each sheets there is a one week schedule of staff. I have a
> > > > > combobox1,command button1and listbox1 in a userform.After you select
> > > > > the name of staff from combobox1, once I click on commandbutton1 I
> > > > > want to display the name of the sheet in the first column of list box
> > > > > and for the second column I want total number of "S" shift of that
> > > > > staff. I wrote the code as follows which I get the sheet names(it
> > > > > means the staff rostered for that week) but could get the countif
> > > > > value. Coz,countif function needs to look for whole row as a range. My
> > > > > question how can a look for a range for each sheet if the range is
> > > > > entire row,or in row 202 from column 1 to 15(thats my range).For a
> > > > > referance i write my code below:
> > > > > Sub TotalShift()
> > > > > Dim cell As Range
> > > > > Dim trddate As Variant
> > > > > Dim xcell As Range
> > > > > Dim trd As Variant
> > > > > Dim Sht As Worksheet
> > > > > trddate = ComboBox1.Text
> > > > > For Each Sht In ThisWorkbook.Sheets
> > > > > Set xcell = Sht.Range("A1:AQ1000")
> > > > > For Each cell In xcell.Columns(1).Cells
> > > > > If cell.Text = trddate Then
> > > > > UserForm1.ListBox1.AddItem Sht.Name
> > > > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > > > 1) = _
> > > > > Application.WorksheetFunction.CountIf(cell.Row, "S")
> > > > > ' !!!! here how to get the range for each sheet
> > > > > End
> > > > > If
> > > > > ' certain row
> > > > > Next cell
> > > > > Next Sht
> > > > > End Sub
> > >
> > > > Hi,
> > > > sorry after I post above query I found out the answer by myself.All I
> > > > need to use
> > > > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > > > but still I get one more question, how can I eliminate the values if
> > > > the column is hidden.Is there any way to deduct the hidden column
> > > > values
> > > > Thanks for the help
> > >
> > > --
> > >
> > > Dave Peterson
> >
> > Thanks Dave but this is not quite I want. Lets say in row number 22 I
> > want to check below
> > x=Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > but if in that sheet if one column hidden and has "S" value, in that
> > case x value will be extra one(actually you might ask why I don`t
> > simply delete that column?
> > I cannot do that,coz there are too many sheets and on each sheets
> > there is always different column is hidden.I am not the one who create
> > that worksheet actually.I just took it from my colleague)
> > Is that possible to skip the value if the column is hidden for that
> > row.\
> >
> > For Each Sht In ThisWorkbook.Sheets
> > Set xcell = Sht.Range("A1:AQ1000")
> > For Each cell In xcell.Columns(1).Cells
> > If cell.Text = trddate Then
> > UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
> > 1).Value
> >
> > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > 1) = _
> > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row),
> > "S") ' here I want to eliminate in that cell.row if the
> > column is hidden and
> >
> >
> > ' the column has "S" value
> >
> > thanks for your help
>
> --
>
> Dave Peterson

--

Dave Peterson

On Feb 17, 7:07 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I used the activecell for testing.
>
> You'd remove that line when you put this code into your loop.
>
>
>
> Dave Peterson wrote:
>
> > Dim cell As Range
> > Dim vRng As Range
> > Dim vArea As Range
> > Dim myCount As Long
>
> > Set cell = ActiveCell
> > Set vRng = Nothing
> > On Error Resume Next
> > Set vRng = cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
> > On Error GoTo 0
>
> > myCount = 0
> > If vRng Is Nothing Then
> > 'no visible cells
> > Else
> > For Each vArea In vRng.Areas
> > myCount = myCount + Application.CountIf(vArea, "X")
> > Next vArea
> > End If
>
> > with UserForm1.ListBox1
> > .List(.ListCount - 1, 1) = myCount
> > End with
>
> > ps.
> > This kind of thing:
> > Sht.Rows(cell.Row)
> > can be written as:
> > cell.entirerow
> > (As long as cell is on sht)
>
> > "bah...@gmail.com" wrote:
>
> > > On Feb 17, 4:00 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > If Cell.EntireColumn.Hidden = True Then
> > > > 'did you mean row?
> > > > 'If Cell.EntireRow.Hidden = True Then
> > > > 'skip it
> > > > Else
> > > > 'do the work
> > > > End If
>
> > > > "bah...@gmail.com" wrote:
>
> > > > > On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> > > > > > Hi,
> > > > > > I have a multiple worksheets in a workbook each refers to one week of
> > > > > > year. Each sheets there is a one week schedule of staff. I have a
> > > > > > combobox1,command button1and listbox1 in a userform.After you select
> > > > > > the name of staff from combobox1, once I click on commandbutton1 I
> > > > > > want to display the name of the sheet in the first column of list box
> > > > > > and for the second column I want total number of "S" shift of that
> > > > > > staff. I wrote the code as follows which I get the sheet names(it
> > > > > > means the staff rostered for that week) but could get the countif
> > > > > > value. Coz,countif function needs to look for whole row as a range. My
> > > > > > question how can a look for a range for each sheet if the range is
> > > > > > entire row,or in row 202 from column 1 to 15(thats my range).For a
> > > > > > referance i write my code below:
> > > > > > Sub TotalShift()
> > > > > > Dim cell As Range
> > > > > > Dim trddate As Variant
> > > > > > Dim xcell As Range
> > > > > > Dim trd As Variant
> > > > > > Dim Sht As Worksheet
> > > > > > trddate = ComboBox1.Text
> > > > > > For Each Sht In ThisWorkbook.Sheets
> > > > > > Set xcell = Sht.Range("A1:AQ1000")
> > > > > > For Each cell In xcell.Columns(1).Cells
> > > > > > If cell.Text = trddate Then
> > > > > > UserForm1.ListBox1.AddItem Sht.Name
> > > > > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > > > > 1) = _
> > > > > > Application.WorksheetFunction.CountIf(cell.Row, "S")
> > > > > > ' !!!! here how to get the range for each sheet
> > > > > > End
> > > > > > If
> > > > > > ' certain row
> > > > > > Next cell
> > > > > > Next Sht
> > > > > > End Sub
>
> > > > > Hi,
> > > > > sorry after I post above query I found out the answer by myself.All I
> > > > > need to use
> > > > > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > > > > but still I get one more question, how can I eliminate the values if
> > > > > the column is hidden.Is there any way to deduct the hidden column
> > > > > values
> > > > > Thanks for the help
>
> > > > --
>
> > > > Dave Peterson
>
> > > Thanks Dave but this is not quite I want. Lets say in row number 22 I
> > > want to check below
> > > x=Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > > but if in that sheet if one column hidden and has "S" value, in that
> > > case x value will be extra one(actually you might ask why I don`t
> > > simply delete that column?
> > > I cannot do that,coz there are too many sheets and on each sheets
> > > there is always different column is hidden.I am not the one who create
> > > that worksheet actually.I just took it from my colleague)
> > > Is that possible to skip the value if the column is hidden for that
> > > row.\
>
> > > For Each Sht In ThisWorkbook.Sheets
> > > Set xcell = Sht.Range("A1:AQ1000")
> > > For Each cell In xcell.Columns(1).Cells
> > > If cell.Text = trddate Then
> > > UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
> > > 1).Value
>
> > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > 1) = _
> > > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row),
> > > "S") ' here I want to eliminate in that cell.row if the
> > > column is hidden and
>
> > > ' the column has "S" value
>
> > > thanks for your help
>
> > --
>
> > Dave Peterson
>
> --
>
> Dave Peterson

Hi Dave,
thanks for your reply but I tried to apply your code as follows,but
still getting "could not get the list property" error.I think I could
not understand clear
Dim cell As Range
Dim trddate As Variant
Dim xcell As Range
Dim vRng As Range
Dim Sht As Worksheet
trddate = ComboBox1.Text
For Each Sht In ThisWorkbook.Sheets
Set xcell = Sht.Range("A1:AQ1000")
For Each cell In xcell.Columns(1).Cells
If cell.Text = trddate Then
Set vRng =
cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
2) = _
Application.CountIf(vRng, "S")
End If
Next cell
Next Sht

Untested, uncompiled. And I'm not sure I got your logic right. It looks like
you're adding the sheetname lots of times.

Dim cell As Range
Dim trddate As Variant
Dim xcell As Range
Dim vRng As Range
Dim Sht As Worksheet
Dim myCount As Long
Dim vArea As Range

'trddate = ComboBox1.Text

For Each Sht In ThisWorkbook.Sheets
Set xcell = Sht.Range("A1:AQ1000")
For Each cell In xcell.Columns(1).Cells
If cell.Text = trddate Then

userform1.listbox1.AddItem Sht.Name

Set vRng = Nothing
On Error Resume Next
Set vRng = cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

myCount = 0
If vRng Is Nothing Then
'no visible cells
Else
For Each vArea In vRng.Areas
myCount = myCount + Application.CountIf(vArea, "X")
Next vArea
End If

With userform1.listbox1
.List(.ListCount - 1, 2) = myCount
End With
End If
Next cell
Next Sht

"(E-Mail Removed)" wrote:
>
> On Feb 17, 7:07 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > I used the activecell for testing.
> >
> > You'd remove that line when you put this code into your loop.
> >
> >
> >
> > Dave Peterson wrote:
> >
> > > Dim cell As Range
> > > Dim vRng As Range
> > > Dim vArea As Range
> > > Dim myCount As Long
> >
> > > Set cell = ActiveCell
> > > Set vRng = Nothing
> > > On Error Resume Next
> > > Set vRng = cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
> > > On Error GoTo 0
> >
> > > myCount = 0
> > > If vRng Is Nothing Then
> > > 'no visible cells
> > > Else
> > > For Each vArea In vRng.Areas
> > > myCount = myCount + Application.CountIf(vArea, "X")
> > > Next vArea
> > > End If
> >
> > > with UserForm1.ListBox1
> > > .List(.ListCount - 1, 1) = myCount
> > > End with
> >
> > > ps.
> > > This kind of thing:
> > > Sht.Rows(cell.Row)
> > > can be written as:
> > > cell.entirerow
> > > (As long as cell is on sht)
> >
> > > "bah...@gmail.com" wrote:
> >
> > > > On Feb 17, 4:00 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > If Cell.EntireColumn.Hidden = True Then
> > > > > 'did you mean row?
> > > > > 'If Cell.EntireRow.Hidden = True Then
> > > > > 'skip it
> > > > > Else
> > > > > 'do the work
> > > > > End If
> >
> > > > > "bah...@gmail.com" wrote:
> >
> > > > > > On Feb 17, 1:41 am, "bah...@gmail.com" <bah...@gmail.com> wrote:
> > > > > > > Hi,
> > > > > > > I have a multiple worksheets in a workbook each refers to one week of
> > > > > > > year. Each sheets there is a one week schedule of staff. I have a
> > > > > > > combobox1,command button1and listbox1 in a userform.After you select
> > > > > > > the name of staff from combobox1, once I click on commandbutton1 I
> > > > > > > want to display the name of the sheet in the first column of list box
> > > > > > > and for the second column I want total number of "S" shift of that
> > > > > > > staff. I wrote the code as follows which I get the sheet names(it
> > > > > > > means the staff rostered for that week) but could get the countif
> > > > > > > value. Coz,countif function needs to look for whole row as a range. My
> > > > > > > question how can a look for a range for each sheet if the range is
> > > > > > > entire row,or in row 202 from column 1 to 15(thats my range).For a
> > > > > > > referance i write my code below:
> > > > > > > Sub TotalShift()
> > > > > > > Dim cell As Range
> > > > > > > Dim trddate As Variant
> > > > > > > Dim xcell As Range
> > > > > > > Dim trd As Variant
> > > > > > > Dim Sht As Worksheet
> > > > > > > trddate = ComboBox1.Text
> > > > > > > For Each Sht In ThisWorkbook.Sheets
> > > > > > > Set xcell = Sht.Range("A1:AQ1000")
> > > > > > > For Each cell In xcell.Columns(1).Cells
> > > > > > > If cell.Text = trddate Then
> > > > > > > UserForm1.ListBox1.AddItem Sht.Name
> > > > > > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > > > > > 1) = _
> > > > > > > Application.WorksheetFunction.CountIf(cell.Row, "S")
> > > > > > > ' !!!! here how to get the range for each sheet
> > > > > > > End
> > > > > > > If
> > > > > > > ' certain row
> > > > > > > Next cell
> > > > > > > Next Sht
> > > > > > > End Sub
> >
> > > > > > Hi,
> > > > > > sorry after I post above query I found out the answer by myself.All I
> > > > > > need to use
> > > > > > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > > > > > but still I get one more question, how can I eliminate the values if
> > > > > > the column is hidden.Is there any way to deduct the hidden column
> > > > > > values
> > > > > > Thanks for the help
> >
> > > > > --
> >
> > > > > Dave Peterson
> >
> > > > Thanks Dave but this is not quite I want. Lets say in row number 22 I
> > > > want to check below
> > > > x=Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row), "S")
> > > > but if in that sheet if one column hidden and has "S" value, in that
> > > > case x value will be extra one(actually you might ask why I don`t
> > > > simply delete that column?
> > > > I cannot do that,coz there are too many sheets and on each sheets
> > > > there is always different column is hidden.I am not the one who create
> > > > that worksheet actually.I just took it from my colleague)
> > > > Is that possible to skip the value if the column is hidden for that
> > > > row.\
> >
> > > > For Each Sht In ThisWorkbook.Sheets
> > > > Set xcell = Sht.Range("A1:AQ1000")
> > > > For Each cell In xcell.Columns(1).Cells
> > > > If cell.Text = trddate Then
> > > > UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
> > > > 1).Value
> >
> > > > UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> > > > 1) = _
> > > > Application.WorksheetFunction.CountIf(Sht.Rows(cell.Row),
> > > > "S") ' here I want to eliminate in that cell.row if the
> > > > column is hidden and
> >
> > > > ' the column has "S" value
> >
> > > > thanks for your help
> >
> > > --
> >
> > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
> Hi Dave,
> thanks for your reply but I tried to apply your code as follows,but
> still getting "could not get the list property" error.I think I could
> not understand clear
> Dim cell As Range
> Dim trddate As Variant
> Dim xcell As Range
> Dim vRng As Range
> Dim Sht As Worksheet
> trddate = ComboBox1.Text
> For Each Sht In ThisWorkbook.Sheets
> Set xcell = Sht.Range("A1:AQ1000")
> For Each cell In xcell.Columns(1).Cells
> If cell.Text = trddate Then
> Set vRng =
> cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible)
> UserForm1.ListBox1.AddItem Sht.Name 'cell.Offset(0,
> 1).Value
> UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
> 2) = _
> Application.CountIf(vRng, "S")
> End If
> Next cell
> Next Sht

--

Dave Peterson

Hi Dave,
Thanks a lot for the help. That was my boss file which she kept all of
our high duty to calculate our monthly bonus. thats why I could not
manipulate muchand there were lots of adding worksheets But just to
understand right why not simply look in vRng instead of vArea? vRng is
already set=cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible) why
it does not work for myCount = myCount + Application.CountIf(vRng,
"X") instead of
myCount = myCount + Application.CountIf(vArea, "X")
Thats the only part I could not understand.
Once again,thanks a lot for the help,the code that you ssent works
perfectly fine.
Have a nice day

Try using =countif() in a worksheet.

=countif(a1:x1,"x")
will work nicely
=countif(a1:c1, e1:m1, q1:x1,"x")
won't work.

=countif() expects a single area range in its first argument.

vArea (my variable name for visual area) is a single area range. It may be lots
of them, but each is a single area.

"(E-Mail Removed)" wrote:
>
> Hi Dave,
> Thanks a lot for the help. That was my boss file which she kept all of
> our high duty to calculate our monthly bonus. thats why I could not
> manipulate muchand there were lots of adding worksheets But just to
> understand right why not simply look in vRng instead of vArea? vRng is
> already set=cell.EntireRow.Cells.SpecialCells(xlCellTypeVisible) why
> it does not work for myCount = myCount + Application.CountIf(vRng,
> "X") instead of
> myCount = myCount + Application.CountIf(vArea, "X")
> Thats the only part I could not understand.
> Once again,thanks a lot for the help,the code that you ssent works
> perfectly fine.
> Have a nice day

--

Dave Peterson