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