Partial match

[dbnfl]

Hi all,

I have the below code and I need to modify so i can search all of a customer to different delivery address.

eg

Smith's C/ TNT
Smith's C/Toll
Smith's C/NFL

I want to be able to just put in Smith's and it gives me all of Smith's jobs.

Sub Report_Extract_Data()

Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim deliverycustomer As String
Dim VesselETAStartDate As Date
Dim DelPUStartDate As Date
Dim Agent As String
Dim finalrow As Integer
Dim i As Integer
Dim Ary As Variant


Set datasheet = Sheet1
Set reportsheet = Sheet12

deliverycustomer = LCase(reportsheet.Range("f3").Value)
DelPUStartDate = LCase(reportsheet.Range("I2").Value)
VesselETAStartDate = LCase(reportsheet.Range("I3").Value)
Agent = LCase(reportsheet.Range("C3").Value)


    reportsheet.Range("B7:AB200").ClearContents
  
    datasheet.Select
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To finalrow
        If LCase(Cells(i, 5)) = Agent Or Agent = "" Then
        If LCase(Cells(i, 8)) = deliverycustomer Or deliverycustomer = "" Then
        If LCase(Cells(i, 42)) >= VesselETAStartDate Then
        If LCase(Cells(i, 58)) >= DelPUStartDate 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, 54, 53, 140))
         reportsheet.Range("B200").End(xlUp).Offset(1, 0).Resize(, 27).Value = Ary
        End If
        End If
        End If
        End If
         'End If
    Next i
   
reportsheet.Select

Range("C3").Select

End Sub

 

[Craggs82]

If instr(LCase(Cells(i, 8)), deliverycustomer) <> 0 Or deliverycustomer = "" Then

should return all values that contain the full 'deliverycustomer' variable anywhere in the string.

 

[dbnfl]

If instr(LCase(Cells(i, 8)), deliverycustomer) <> 0 Or deliverycustomer = "" Then

should return all values that contain the full 'deliverycustomer' variable anywhere in the string.

Hello,

Thank you so much. Works perfect.

Dale.