Odd macro behavior

D

Dave Birley

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

Dave Birley

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

Vergel Adriano

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
D

Dave Birley

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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
V

Vergel Adriano

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


:

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?
Click to expand...
Click to expand...
Click to expand...
 
M

merjet

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
 
D

Dave Birley

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

Ask a Question

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

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

Ask a Question

Similar Threads

Reference Numeric Variable in Macro 7
Code to delete names works in one WB, but not another WB? 2
Macro to highlight rows and... 1
Selecting a Range based on an IF condition 3
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
shorten sheet name 4
Modifying For Loop 3
Macro fails in different WB 3

Top