Extracting Data

[dbnfl]

Hello,

I need to adjust code below that it will always return a match if the following "Ok to Book" "Slotted" "Delivery Pending" are found in column 3 of my data Sheet (Sheet1). I tried adding in line of code in Green, you can click search and screen will flick but will not return anything. If I remove added code it will work to search Job Status selection Reportsheet C3.

Any help would be greatly appreciated

Sub Planning_Extract_Data()

Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim jobstatus As String
Dim jobstatus2 As String
Dim Agent As String
Dim jobtype As String
Dim finalrow As Integer
Dim i As Integer
Dim Ary As Variant

Set datasheet = Sheet1
Set reportsheet = Sheet5

jobstatus = LCase(reportsheet.Range("C3").Value)
Agent = LCase(reportsheet.Range("E3").Value)
jobtype = LCase(reportsheet.Range("G3").Value)


reportsheet.Range("B7:AA200").ClearContents

datasheet.Select
finalrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To finalrow

'If datasheet.Cells(i, 3).Value = "Ok to Book" or "Slotted" or "Delivery Pending" Then (jobstatus)
If LCase(Cells(i, 3)) = jobstatus Or jobstatus = "" Then
If LCase(Cells(i, 5)) = Agent Or Agent = "" Then
If LCase(Cells(i, 4)) = jobtype Or jobtype = "" Then
Ary = Application.Index(Rows(i), 1, Array(2, 3, 5, 6, 8, 10, 16, 18, 19, 58, 59, 29, 51, 41, 42, 43, 44, 46, 47, 49, 35, 36, 37, 34, 57, 53))
reportsheet.Range("B200").End(xlUp).Offset(1, 0).Resize(, 26).Value = Ary
'End If
End If
End If
End If
Next i

reportsheet.Select

Range("C3").Select

End Sub

 

[Michael M]

It needs to be

If Cells(i, 3).Value = "Ok to Book" or Cells(i, 3).Value ="Slotted" or Cells(i, 3).Value ="Delivery Pending"

 

[Peter_SSs]

A bit more compact alternative is

If InStr(1, "|Ok to Book|Slotted|Delivery Pending|", "|" & datasheet.Cells(i, 3).Value & "|", 1) > 0 Then

 

[dbnfl]

It needs to be

If Cells(i, 3).Value = "Ok to Book" or Cells(i, 3).Value ="Slotted" or Cells(i, 3).Value ="Delivery Pending"

Thank you so much. Works like a charm.

 

[dbnfl]

A bit more compact alternative is

If InStr(1, "|Ok to Book|Slotted|Delivery Pending|", "|" & datasheet.Cells(i, 3).Value & "|", 1) > 0 Then

thank you for the reply. I used Michael M's line of code and its working nicely. I will also try your one as well.

 

[Peter_SSs]

I used Michael M's line of code and its working nicely. I will also try your one as well.

No problem, use whichever you feel comfortable with and works.