Split cell info via VB problem
 

This was posted yesterday, with an unclear subject line. Sorry.
Hopefully, this subject line will draw some helpful suggestions.
========================================
Both# 1 and # 2, below, were made by doing an actual live macro recording.
The first one doesn't work, the second one does.
Am I overlooking something obvious?

1: ' Select JO# (left Side Only) from A5 and Paste in C3
Range("C3").Select
ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1))"
Range("C4").Select

2 ' Select JO# (Right Side Only) from A5 and Paste in B5
' Copy down to Last Row
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=VALUE(RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])))"
Range("B6").Select

Split cell info via VB problem
 

the first one has an extra ")" on the end. These worked for me:

Sub aBC()
Range("C3").Select
ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1)"
Range("C4").Select

' Select JO# (Right Side Only) from A5 and Paste in B5
' Copy down to Last Row
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=VALUE(RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])))"
Range("B6").Select
End Sub

--
Regards,
Tom Ogilvy


"BEEJAY" wrote:

This was posted yesterday, with an unclear subject line. Sorry.
Hopefully, this subject line will draw some helpful suggestions.
========================================
Both# 1 and # 2, below, were made by doing an actual live macro recording.
The first one doesn't work, the second one does.
Am I overlooking something obvious?

1: ' Select JO# (left Side Only) from A5 and Paste in C3
Range("C3").Select
ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1))"
Range("C4").Select

2 ' Select JO# (Right Side Only) from A5 and Paste in B5
' Copy down to Last Row
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=VALUE(RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])))"
Range("B6").Select

Split cell info via VB problem
 

BeeJay,

You're overlooking the extra paren at the end of this line:

ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1))"

It should be:

ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1)"

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
This was posted yesterday, with an unclear subject line. Sorry.
Hopefully, this subject line will draw some helpful suggestions.
========================================
Both# 1 and # 2, below, were made by doing an actual live macro recording.
The first one doesn't work, the second one does.
Am I overlooking something obvious?

1: ' Select JO# (left Side Only) from A5 and Paste in C3
Range("C3").Select
ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1))"
Range("C4").Select

2 ' Select JO# (Right Side Only) from A5 and Paste in B5
' Copy down to Last Row
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=VALUE(RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])))"
Range("B6").Select

Split cell info via VB problem
 

Gentlemen:
Thank-you both. Right on the money.
The frustrating part was that when I made the corrections, it still did not
work.
I then re-typed the line, and it still did not work.
I then cut an paste from this post, and it worked great.
Go figure.


"Bernie Deitrick" wrote:

BeeJay,

You're overlooking the extra paren at the end of this line:

ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1))"

It should be:

ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1)"

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
This was posted yesterday, with an unclear subject line. Sorry.
Hopefully, this subject line will draw some helpful suggestions.
========================================
Both# 1 and # 2, below, were made by doing an actual live macro recording.
The first one doesn't work, the second one does.
Am I overlooking something obvious?

1: ' Select JO# (left Side Only) from A5 and Paste in C3
Range("C3").Select
ActiveCell.FormulaR1C1 = "=LEFT(R[2]C[-2],FIND(""-"",R[2]C[-2])-1))"
Range("C4").Select

2 ' Select JO# (Right Side Only) from A5 and Paste in B5
' Copy down to Last Row
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=VALUE(RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])))"
Range("B6").Select