About Us

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

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

"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

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

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

"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

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

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

"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

Basta1980

Joel,

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

Set c = .FindNext(after:=c)

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

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

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

Basta1980

Okay, I changed the command line, but I still get the same run-time error

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

Basta1980

Could it be because you do the 'set c' twice?!

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

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

"Basta1980" wrote:

Okay, I changed the command line, but I still get the same run-time error

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

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

"Joel" wrote:

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

"Basta1980" wrote:

Okay, I changed the command line, but I still get the same run-time error

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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!

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

"Basta1980" wrote:

Could it be because you do the 'set c' twice?!

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

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

"Basta1980" wrote:

Could it be because you do the 'set c' twice?!

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

Basta1980

Joel,

Same situation here, still looks like the code is copying from B2

"Joel" wrote:

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

"Basta1980" wrote:

Could it be because you do the 'set c' twice?!

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

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

"Basta1980" wrote:

Joel,

Same situation here, still looks like the code is copying from B2

"Joel" wrote:

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

"Basta1980" wrote:

Could it be because you do the 'set c' twice?!

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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?!

[email protected][_2_]

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.

Basta1980

Joel,

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

Gr.

Basta

"Joel" wrote:

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

"Basta1980" wrote:

Joel,

Same situation here, still looks like the code is copying from B2

"Joel" wrote:

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

"Basta1980" wrote:

Could it be because you do the 'set c' twice?!

"Joel" wrote:

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)

"Basta1980" wrote:

Joel,

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

Set c = .FindNext(after:=c)

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

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Lookup Formula: Return 1st match, then 2nd match, then 3rd match	Scott	Excel Discussion (Misc queries)	4	December 11th 09 05:50 AM
match one column with dates to several colums with dates, copy pas	Torben	Excel Programming	4	November 3rd 08 04:10 PM
Trying to match up variables to a common list and confirm	DebKnight56	Excel Worksheet Functions	12	July 26th 07 09:40 PM
Lookup? Match? pulling rows from one spreadsheet to match a text f	cjax	Excel Worksheet Functions	3	July 21st 06 02:51 PM
Lookup or Match	mhd143	Excel Programming	1	July 10th 06 08:44 PM

Menu

About Us