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