Lookup, match dates and confirm fase

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
 
J

Joel

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
 
B

Basta1980

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
 
J

Joel

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
 
B

Basta1980

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
 
J

Joel

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
 
B

Basta1980

Joel,

I get a run-time erro 438 and VBA points toward

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

Joel

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)
 
B

Basta1980

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...
 
J

Joel

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
 
B

Basta1980

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
 
J

Joel

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
 
J

Joel

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
 
J

Joel

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
 
G

gimme_this_gimme_that

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.
 
B

Basta1980

Joel,

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

Gr.

Basta
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match columns to rows then loop. 2
MATCH command does not work with dates 3
Sumproduct function allow a lookup for one Date to check if it fallsin Date Range in other table 9
Create if partial match then copy and paste data from one cell to another 1
data lookup/validation syntax 2
Populating values in cells based on lookup from other cell. 4
Match based on a lookup and then flag 4
Match Values and Highlite row 2

Top