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.
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.
Rich (BB code):
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
Last edited by a moderator: