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.ListCou nt - 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, " 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.ListCou nt - 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(cel l.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

" wrote:

On Feb 17, 1:41 am, " 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.ListCou nt - 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(cel l.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 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



" wrote:

On Feb 17, 1:41 am, " 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.ListCou nt - 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(cel l.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(c ell.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.ListCou nt - 1,
1) = _
Application.WorksheetFunction.CountIf(Sht.Rows(cel l.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(xlCellTypeVisibl e)
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)





" wrote:

On Feb 17, 4:00 am, Dave Peterson 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



" wrote:

On Feb 17, 1:41 am, " 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.ListCou nt - 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(cel l.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(c ell.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.ListCou nt - 1,
1) = _
Application.WorksheetFunction.CountIf(Sht.Rows(cel l.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(xlCellTypeVisibl e)
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)

" wrote:

On Feb 17, 4:00 am, Dave Peterson 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



" wrote:

On Feb 17, 1:41 am, " 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.ListCou nt - 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(cel l.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(c ell.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.ListCou nt - 1,
1) = _
Application.WorksheetFunction.CountIf(Sht.Rows(cel l.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