Lookup, match dates and confirm fase

  • Thread starter Thread starter Basta1980
  • Start date Start date
B

Basta1980

Hi all,

I have 2 sheets (Sheet1 and Sheet2). In sheet1 column A lists customers and
column B lists Issue Dates. In sheet2 Column A lists customers, Column B a
date (pre-sale), Column C a date (test date) and column D also a date (actual
launch). What I want to do is lookup the customer listed in column A sheet1
in Column A sheet2, if there’s a match check if the issue date in column B
sheet1 is either pre-sale, test or actual launch (so check if it is smaller
or larger than dates in column B:D sheet2). How do I go about?!

Many thanks in advance!

Basta1980
 
Sub GetStatus()

With Sheets("Sheet2")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)
With Sheets("Sheet1")
Set c = .Columns("A").Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
Else
IssueDate = c.Offset(0, 1)
End If
End With

If Not c Is Nothing Then
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

Select Case IssueDate
Case Is <= PreDate
.Range("E" & RowCount) = "Pre Date"
Case Is <= TestDate
.Range("E" & RowCount) = "Test Date"
Case Is <= PreDate
.Range("E" & RowCount) = "Inbetween"
Case Is >= ActualDate
.Range("E" & RowCount) = "Actual Date"
Case Else
.Range("E" & RowCount) = "Bad Date"
End Select
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
Hi Joel,

Thank you very much. Three minor things;

1. What do I have to adjust when I use column headings?!
2. Is it also possible to switch outcome (IssueDate = c.offset(0.1) ) to an
alternate place in the workbook (i.e. sheet1 as this is the main sheet)?!
3. IF you have the time; what do the first two sections mean/work?! The Case
structure is claer to me, but what do the other statements do?!

Basta
 
Sub GetStatus()

With Sheets("Sheet2")
'Skip Header row
RowCount = 2
'look through every row of worksheet until a blank is found
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)

With Sheets("Sheet1")
'search column A for cutomer name in sheet 1
Set c = .Columns("A").Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
Else
IssueDate = c.Offset(0, 1)
End If
End With

If Not c Is Nothing Then
'if customer is found get dates
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

'compare issue dates against
Select Case IssueDate
Case Is <= PreDate
'Issued Date is before Pre Date
c.Offset(0, 2) = "Pre Date"
Case Is <= TestDate
'Issue date is greater than Pre Date and less than Test Date
c.Offset(0, 2) = "Test Date"
Case Is <= ActualDate
'Issue date is greater than test date and less than actual date
c.Offset(0, 2) = "Inbetween"
Case Is >= ActualDate
'Issue Date is greatter tan actual date
c.Offset(0, 2) = "Actual Date"
Case Else
'we should never get here
c.Offset(0, 2) = "Bad Date"
End Select
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
Hi Joel,

Thanks, one more thing though. I have a list of customers which are listed
more than once. With the code below I get a hit just once per customer, but I
need a hit per case. So if customer Jones is listed twice with various dates
it should tell me for both listings what phase it is.

Regards


Basta
 
Sub GetStatus()

With Sheets("Sheet2")
'Skip Header row
RowCount = 2
'look through every row of worksheet until a blank is found
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)

With Sheets("Sheet1")
'search column A for cutomer name in sheet 1
Set c = .Columns("A").Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
Else
IssueDate = c.Offset(0, 1)
End If
End With

If Not c Is Nothing Then
'if customer is found get dates
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

FirstAddress = c.Address
Do

'compare issue dates against
Select Case IssueDate
Case Is <= PreDate
'Issued Date is before Pre Date
c.Offset(0, 2) = "Pre Date"
Case Is <= TestDate
'Issue date is greater than Pre Date
'and less than Test Date
c.Offset(0, 2) = "Test Date"
Case Is <= ActualDate
'Issue date is greater than test date
'and less than actual date
c.Offset(0, 2) = "Inbetween"
Case Is >= ActualDate
'Issue Date is greatter tan actual date
c.Offset(0, 2) = "Actual Date"
Case Else
'we should never get here
c.Offset(0, 2) = "Bad Date"
End Select

Set c = .FindNext(after:=c)

Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
I copied the line from the VBA help FINDNEXT not noticing the period infront
of the FindNext. With my code it is looking on sheet 2 instead of Sheet 1

from
Set c = .FindNext(after:=c)
to
Set c = Sheets("Sheet1").FindNext(after:=c)
 
Could it be because you do the 'set c' twice?!

Joel said:
I copied the line from the VBA help FINDNEXT not noticing the period infront
of the FindNext. With my code it is looking on sheet 2 instead of Sheet 1

from
Set c = .FindNext(after:=c)
to
Set c = Sheets("Sheet1").FindNext(after:=c)
Click to expand...
 
I didn't realize that FinNext didn't work with columns. I had to change the
search range to a Range object instead of a column object.

Sub GetStatus()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SearchRange = .Range("A1:A" & LastRow)
End With

With Sheets("Sheet2")
'Skip Header row
RowCount = 2
'look through every row of worksheet until a blank is found
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)

With Sheets("Sheet1")
'search column A for cutomer name in sheet 1
Set c = SearchRange.Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
Else
IssueDate = c.Offset(0, 1)
End If
End With

If Not c Is Nothing Then
'if customer is found get dates
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

FirstAddress = c.Address
Do

'compare issue dates against
Select Case IssueDate
Case Is <= PreDate
'Issued Date is before Pre Date
c.Offset(0, 2) = "Pre Date"
Case Is <= TestDate
'Issue date is greater than Pre Date
'and less than Test Date
c.Offset(0, 2) = "Test Date"
Case Is <= ActualDate
'Issue date is greater than test date
'and less than actual date
c.Offset(0, 2) = "Inbetween"
Case Is >= ActualDate
'Issue Date is greatter tan actual date
c.Offset(0, 2) = "Actual Date"
Case Else
'we should never get here
c.Offset(0, 2) = "Bad Date"
End Select

Set c = SearchRange.FindNext(after:=c)

Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
Joel,

The code copies the same result form the first line

sheet1=

Customer Issue Date Phase.
123456789 30/06/2004 Pre Date
123456789 01/07/2008 Pre Date
123456789 23/12/2009 Pre Date
123456789 24/12/2009 Pre Date
123456789 25/12/2009 Pre Date
123456789 02/02/2010 Pre Date
123456789 01/01/2010 Pre Date
987654321 30/05/2008 Pre Date
987654321 01/06/2008 Pre Date
987654321 18/08/2009 Pre Date
987654321 19/08/2009 Pre Date
987654321 01/06/2010 Pre Date
987654321 01/09/2010 Pre Date

sheet2

Customer Name Pre-fase Test Launch
123456789 01/01/2008 01/01/2009 01/01/2010
987654321 01/06/2008 01/06/2009 01/06/2010

As you can see regardless of value in sheet1 cel b3 and so forth the outcome
is always Pre-Date
 
THIS WILL WORK. I had to move one line of code.

Sub GetStatus()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SearchRange = .Range("A1:A" & LastRow)
End With

With Sheets("Sheet2")
'Skip Header row
RowCount = 2
'look through every row of worksheet until a blank is found
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)

With Sheets("Sheet1")
'search column A for cutomer name in sheet 1
Set c = SearchRange.Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
End If
End With

If Not c Is Nothing Then

IssueDate = c.Offset(0, 1)

'if customer is found get dates
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

FirstAddress = c.Address
Do

'compare issue dates against
Select Case IssueDate
Case Is <= PreDate
'Issued Date is before Pre Date
c.Offset(0, 2) = "Pre Date"
Case Is <= TestDate
'Issue date is greater than Pre Date
'and less than Test Date
c.Offset(0, 2) = "Test Date"
Case Is <= ActualDate
'Issue date is greater than test date
'and less than actual date
c.Offset(0, 2) = "Inbetween"
Case Is >= ActualDate
'Issue Date is greatter tan actual date
c.Offset(0, 2) = "Actual Date"
Case Else
'we should never get here
c.Offset(0, 2) = "Bad Date"
End Select

Set c = SearchRange.FindNext(after:=c)

Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
looking at your data my tests weren't correct. Try this code

Sub GetStatus()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SearchRange = .Range("A1:A" & LastRow)
End With

With Sheets("Sheet2")
'Skip Header row
RowCount = 2
'look through every row of worksheet until a blank is found
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)

With Sheets("Sheet1")
'search column A for cutomer name in sheet 1
Set c = SearchRange.Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
End If
End With

If Not c Is Nothing Then

IssueDate = c.Offset(0, 1)

'if customer is found get dates
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

FirstAddress = c.Address
Do

'compare issue dates against
Select Case IssueDate
Case Is < TestDate
'Issued Date is before Test Date
c.Offset(0, 2) = "Pre Date"
Case Is < ActualDate
'Issue date is greater than Test Date
'and less than Actual Date
c.Offset(0, 2) = "Test Date"
Case Is >= ActualDate
'Issue date is greater than Actual Date
c.Offset(0, 2) = "Actual Date"
Case Else
'we should never get here
c.Offset(0, 2) = "Bad Date"
End Select

Set c = SearchRange.FindNext(after:=c)

Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
I put the line below in the wrong place
IssueDate = c.Offset(0, 1)



Sub GetStatus()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SearchRange = .Range("A1:A" & LastRow)
End With

With Sheets("Sheet2")
'Skip Header row
RowCount = 2
'look through every row of worksheet until a blank is found
Do While .Range("A" & RowCount) <> ""
Customer = .Range("A" & RowCount)

With Sheets("Sheet1")
'search column A for cutomer name in sheet 1
Set c = SearchRange.Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Customer : " & Customer)
End If
End With

If Not c Is Nothing Then


'if customer is found get dates
PreDate = .Range("B" & RowCount)
TestDate = .Range("C" & RowCount)
ActualDate = .Range("D" & RowCount)

FirstAddress = c.Address
Do
IssueDate = c.Offset(0, 1)

'compare issue dates against
Select Case IssueDate
Case Is < TestDate
'Issued Date is before Test Date
c.Offset(0, 2) = "Pre Date"
Case Is < ActualDate
'Issue date is greater than Test Date
'and less than Actual Date
c.Offset(0, 2) = "Test Date"
Case Is >= ActualDate
'Issue date is greater than Actual Date
c.Offset(0, 2) = "Actual Date"
Case Else
'we should never get here
c.Offset(0, 2) = "Bad Date"
End Select

Set c = SearchRange.FindNext(after:=c)
z = c.Address

Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
RowCount = RowCount + 1
Loop
End With
End Sub
 
The code for this will be easier to maintain - as well as be much
faster - if you use ADO instead of this row-at-a-time stuff.
 
Joel,

Sorry for the somewhat delayed reaction, everything works fine. Thank u very
much!

Gr.

Basta
 
Back
Top