Hi,

Iwant to search 10 columns of data one row at a time (50 rows). If
the row contains the value "SL" then I want to add the value of the
first column of the current row to a variable. I've tried looping
through the entire range, but I then end up with duplicated data. I
need to move to the next row once the first instance of "SL" is found.

I have this so far (but it does not work

Sub test
Dim SickStaff As String
Dim d As Range

For Each d In ThisWorkbook.Sheets("AWD Grid").Range("G2:BB1000")
If d.Value = "SL" Then
SickStaff = SickStaff & Cells(d.Row, 1).Value & ", "
End If
Next d

End sub
Hope you can help.

Sub test
Dim SickStaff As String
Can you give more details. You are sreaching from G2:BB1000 which is more
than 10 columns and more than 50 rows. Which is right. The Range G2:BB1000
or your description of 10 columns and 50 rows?

"MJKelly" wrote:


Hi,

Iwant to search 10 columns of data one row at a time (50 rows). If
the row contains the value "SL" then I want to add the value of the
first column of the current row to a variable. I've tried looping
through the entire range, but I then end up with duplicated data. I
need to move to the next row once the first instance of "SL" is found.

I have this so far (but it does not work

Sub test
Dim SickStaff As String
Dim d As Range

For Each d In ThisWorkbook.Sheets("AWD Grid").Range("G2:BB1000")
If d.Value = "SL" Then
SickStaff = SickStaff & Cells(d.Row, 1).Value & ", "
End If
Next d

End sub
Hope you can help.

On Dec 16, 11:30*am, Joel wrote:
Sub test
Dim SickStaff As String
Can you give more details. *You are sreaching from G2:BB1000 which is more
than 10 columns and more than 50 rows. *Which is right. *The Range G2:BB1000 *
or your description of 10 columns and 50 rows?



"MJKelly" wrote:

Hi,

Iwant to search 10 columns of data one row at a time (50 rows). *If
the row contains the value "SL" then I want to add the value of the
first column of the current row to a variable. *I've tried looping
through the entire range, but I then end up with duplicated data. *I
need to move to the next row once the first instance of "SL" is found.

I have this so far (but it does not work

Sub test
Dim SickStaff As String
Dim d As Range

For Each d In ThisWorkbook.Sheets("AWD Grid").Range("G2:BB1000")
* * If d.Value = "SL" Then
* * * * SickStaff = SickStaff & Cells(d.Row, 1).Value & ", "
* * End If
Next d

End sub
Hope you can help.- Hide quoted text -

- Show quoted text -

Sorry,
I started writing the problem and tried to use easy numbers. I added
the code to show where I had got so far. I actualy want to search the
range in the code.

Many thanks,
Matt

Are you looking for ONLY one instance of "SL" or many. If you are saying to
find one.

Sub FindOneSL()
On Error GoTo NotThere
mv = "ss " & Cells.Find(What:="SL", After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

MsgBox mv
Exit Sub
NotThe MsgBox "Not There"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MJKelly" wrote in message
...

Hi,

Iwant to search 10 columns of data one row at a time (50 rows). If
the row contains the value "SL" then I want to add the value of the
first column of the current row to a variable. I've tried looping
through the entire range, but I then end up with duplicated data. I
need to move to the next row once the first instance of "SL" is found.

I have this so far (but it does not work

Sub test
Dim SickStaff As String
Dim d As Range

For Each d In ThisWorkbook.Sheets("AWD Grid").Range("G2:BB1000")
If d.Value = "SL" Then
SickStaff = SickStaff & Cells(d.Row, 1).Value & ", "
End If
Next d

End sub
Hope you can help.

Sub test()
Dim SickStaff As String
Dim d As Range

With ThisWorkbook.Sheets("AWD Grid")
SickStaff = ""
For RowCount = 2 To 1000
For ColCount = .Range("G2").Column To .Range("BB2").Column
If .Cells(RowCount, ColCount) = "SL" Then
If SickStaff = "" Then
SickStaff = Cells(RowCount, "A").Value
Else
SickStaff = SickStaff & ", " & Cells(RowCount, "A").Value
End If
Exit For 'only lists one item per row
End If
Next ColCount
Next RowCount
End With

End Sub

"MJKelly" wrote:

On Dec 16, 11:30 am, Joel wrote:
Sub test
Dim SickStaff As String
Can you give more details. You are sreaching from G2:BB1000 which is more
than 10 columns and more than 50 rows. Which is right. The Range G2:BB1000
or your description of 10 columns and 50 rows?



"MJKelly" wrote:

Hi,

Iwant to search 10 columns of data one row at a time (50 rows). If
the row contains the value "SL" then I want to add the value of the
first column of the current row to a variable. I've tried looping
through the entire range, but I then end up with duplicated data. I
need to move to the next row once the first instance of "SL" is found.

I have this so far (but it does not work

Sub test
Dim SickStaff As String
Dim d As Range

For Each d In ThisWorkbook.Sheets("AWD Grid").Range("G2:BB1000")
If d.Value = "SL" Then
SickStaff = SickStaff & Cells(d.Row, 1).Value & ", "
End If
Next d

End sub
Hope you can help.- Hide quoted text -

- Show quoted text -

Sorry,
I started writing the problem and tried to use easy numbers. I added
the code to show where I had got so far. I actualy want to search the
range in the code.

Many thanks,
Matt