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 theres 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





"Basta1980" wrote:

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 theres 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

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

"Joel" wrote:

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





"Basta1980" wrote:

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 theres 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")
'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






"Basta1980" wrote:

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

"Joel" wrote:

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





"Basta1980" wrote:

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 theres 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

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
"Joel" wrote:

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






"Basta1980" wrote:

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

"Joel" wrote:

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





"Basta1980" wrote:

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 theres 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")
'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






"Basta1980" wrote:

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
"Joel" wrote:

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






"Basta1980" wrote:

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

"Joel" wrote:

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





"Basta1980" wrote:

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 theres 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

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.