Here's a macro that ALMOST works perfectly:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0
For Each myCell In Range("E6", Range("E65536").End(xlUp))
If myCell.Value & myCell(1, -2).Value & myCell(1, -1).Value < myCell(2,
1).Value & myCell(2, -2).Value & myCell(2, -1).Value Then
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell
End Sub

Rows C, D, and E are the ones referenced in the above. C contains the Last
name, D contains the First name, and E contains a year value (text). I am
writing out the concatenated Last Name, First Name pair into a vacant cell to
the right of other data.

The idea here is that every time there is a change of ANY OF THE THREE
components, the "Then" result fires. And it does -- ALMOST every time.

The one exception is when only the First Name changes. Then "Then" doesn't
fire.

Example:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then fires"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then does NOT fire!"

Any ideas what is going on?

--
Dave
Temping with Staffmark
in Rock Hill, SC

Ooops -- that data sample whould read this way:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then does NOT fire!"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then fires"

Sorry!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

Here's a macro that ALMOST works perfectly:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0
For Each myCell In Range("E6", Range("E65536").End(xlUp))
If myCell.Value & myCell(1, -2).Value & myCell(1, -1).Value < myCell(2,
1).Value & myCell(2, -2).Value & myCell(2, -1).Value Then
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell
End Sub

Rows C, D, and E are the ones referenced in the above. C contains the Last
name, D contains the First name, and E contains a year value (text). I am
writing out the concatenated Last Name, First Name pair into a vacant cell to
the right of other data.

The idea here is that every time there is a change of ANY OF THE THREE
components, the "Then" result fires. And it does -- ALMOST every time.

The one exception is when only the First Name changes. Then "Then" doesn't
fire.

Example:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then fires"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then does NOT fire!"

Any ideas what is going on?

--
Dave
Temping with Staffmark
in Rock Hill, SC

Dave,

I think this is what you're trying to do.. give this a try:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0

For Each myCell In Range("E6", Range("E65536").End(xlUp))
With myCell
If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
..Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value Then
myCell.Offset(0, 13).FormulaR1C1 = "=RC[-15] & "", "" & RC[-14]"
myCount = 0
Else
myCount = myCount + 1
End If
End With
Next myCell

End Sub


--
Hope that helps.

Vergel Adriano


"Dave Birley" wrote:

Ooops -- that data sample whould read this way:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then does NOT fire!"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then fires"

Sorry!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

Here's a macro that ALMOST works perfectly:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0
For Each myCell In Range("E6", Range("E65536").End(xlUp))
If myCell.Value & myCell(1, -2).Value & myCell(1, -1).Value < myCell(2,
1).Value & myCell(2, -2).Value & myCell(2, -1).Value Then
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell
End Sub

Rows C, D, and E are the ones referenced in the above. C contains the Last
name, D contains the First name, and E contains a year value (text). I am
writing out the concatenated Last Name, First Name pair into a vacant cell to
the right of other data.

The idea here is that every time there is a change of ANY OF THE THREE
components, the "Then" result fires. And it does -- ALMOST every time.

The one exception is when only the First Name changes. Then "Then" doesn't
fire.

Example:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then fires"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then does NOT fire!"

Any ideas what is going on?

--
Dave
Temping with Staffmark
in Rock Hill, SC

It throws me a Compile Error -- Syntax Error on that "If" expression.

If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
..Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value Then

Unfortunately I am not familiar enough with VBA to know what I'm seeing
here, so I have no idea yet how to hack it.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Vergel Adriano" wrote:

Dave,

I think this is what you're trying to do.. give this a try:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0

For Each myCell In Range("E6", Range("E65536").End(xlUp))
With myCell
If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
.Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value Then
myCell.Offset(0, 13).FormulaR1C1 = "=RC[-15] & "", "" & RC[-14]"
myCount = 0
Else
myCount = myCount + 1
End If
End With
Next myCell

End Sub


--
Hope that helps.

Vergel Adriano


"Dave Birley" wrote:

Ooops -- that data sample whould read this way:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then does NOT fire!"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then fires"

Sorry!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

Here's a macro that ALMOST works perfectly:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0
For Each myCell In Range("E6", Range("E65536").End(xlUp))
If myCell.Value & myCell(1, -2).Value & myCell(1, -1).Value < myCell(2,
1).Value & myCell(2, -2).Value & myCell(2, -1).Value Then
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell
End Sub

Rows C, D, and E are the ones referenced in the above. C contains the Last
name, D contains the First name, and E contains a year value (text). I am
writing out the concatenated Last Name, First Name pair into a vacant cell to
the right of other data.

The idea here is that every time there is a change of ANY OF THE THREE
components, the "Then" result fires. And it does -- ALMOST every time.

The one exception is when only the First Name changes. Then "Then" doesn't
fire.

Example:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then fires"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then does NOT fire!"

Any ideas what is going on?

--
Dave
Temping with Staffmark
in Rock Hill, SC

I think it's because half of the If - Then statement wrapped to the next
line. Try to copy paste this:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0

For Each myCell In Range("E6", Range("E65536").End(xlUp))
With myCell
If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value < _
.Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1,
-2).Value Then
myCell.Offset(0, 13).FormulaR1C1 = "=RC[-15] & "", "" & RC[-14]"
myCount = 0
Else
myCount = myCount + 1
End If
End With
Next myCell

End Sub



--
Hope that helps.

Vergel Adriano


"Dave Birley" wrote:

It throws me a Compile Error -- Syntax Error on that "If" expression.

If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
.Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value Then

Unfortunately I am not familiar enough with VBA to know what I'm seeing
here, so I have no idea yet how to hack it.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Vergel Adriano" wrote:

Dave,

I think this is what you're trying to do.. give this a try:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0

For Each myCell In Range("E6", Range("E65536").End(xlUp))
With myCell
If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
.Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value Then
myCell.Offset(0, 13).FormulaR1C1 = "=RC[-15] & "", "" & RC[-14]"
myCount = 0
Else
myCount = myCount + 1
End If
End With
Next myCell

End Sub


--
Hope that helps.

Vergel Adriano


"Dave Birley" wrote:

Ooops -- that data sample whould read this way:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then does NOT fire!"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then fires"

Sorry!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

Here's a macro that ALMOST works perfectly:

Sub TryNew()
Dim myCount As Integer
Dim myCell As Range
myCount = 0
For Each myCell In Range("E6", Range("E65536").End(xlUp))
If myCell.Value & myCell(1, -2).Value & myCell(1, -1).Value < myCell(2,
1).Value & myCell(2, -2).Value & myCell(2, -1).Value Then
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell
End Sub

Rows C, D, and E are the ones referenced in the above. C contains the Last
name, D contains the First name, and E contains a year value (text). I am
writing out the concatenated Last Name, First Name pair into a vacant cell to
the right of other data.

The idea here is that every time there is a change of ANY OF THE THREE
components, the "Then" result fires. And it does -- ALMOST every time.

The one exception is when only the First Name changes. Then "Then" doesn't
fire.

Example:

SMITH JOHN 1999
SMITH JOHN 1999 "Then fires"
SMYTHE JOHN 1999
SMYTHE JOHN 1999 "Then fires"
SMYTHE JANE 1999
SMYTHE JANE 1999 "Then does NOT fire!"

Any ideas what is going on?

--
Dave
Temping with Staffmark
in Rock Hill, SC

You are probably getting the compile error because this:

If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
..Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value
Then

should be one line in your code. When long lines are posted,
the text wraps. So make it one line or put a continuation
character at the end of the first.

Another possible problem is your use of, for example, myCell(1,
-2).Value.
Maybe you believe this refers to the same row 2 columns left.
However, it refers to 3 columns left. If you use Offset like Vergel
did, the code is intuitively clearer.

Hth,
Merjet

Thanks to both of you, and a BIG "DUH!!!" to me! I normally post copied code
into the NotePad and get rid of unexpected carriage-returns -- failed to do
it this time.

Kablooey! Don't worry, folks, I'll get the hang of the VBA pretty quickly <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"merjet" wrote:

You are probably getting the compile error because this:

If .Value & .Offset(0, -1).Value & .Offset(0, -2).Value <
..Offset(1, 0).Value & .Offset(1, -1).Value & .Offset(1, -2).Value
Then

should be one line in your code. When long lines are posted,
the text wraps. So make it one line or put a continuation
character at the end of the first.

Another possible problem is your use of, for example, myCell(1,
-2).Value.
Maybe you believe this refers to the same row 2 columns left.
However, it refers to 3 columns left. If you use Offset like Vergel
did, the code is intuitively clearer.

Hth,
Merjet