search a row for data then move to next row

[MJKelly]

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.

 

[Joel]

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]

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?










- 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

 

[Don Guillett]

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
NotThere: MsgBox "Not There"
End Sub

 

[Joel]

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