search a row for data then move to next row

  • Thread starter Thread starter MJKelly
  • Start date Start date
M

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.
 
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?
 
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 -
Click to expand...

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