countif from whole row as range

[baha17]

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

 

[baha17]

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]

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

 

[baha17]

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

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]

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)

 

[Dave Peterson]

I used the activecell for testing.

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

 

[baha17]

I used the activecell for testing.

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

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]

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

 

[baha17]

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]

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.

 

[baha17]

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.

Thanks Dave I got it,
have a nice day