Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move cell info and info in range of cells on new entry | Excel Discussion (Misc queries) | |||
Move cell info and info in neighboring cell on new entry | Excel Discussion (Misc queries) | |||
Split out info in a Cell | Excel Programming | |||
Link info in one cell to info in several cells in another column (like a database) | Excel Discussion (Misc queries) | |||
Trying to split up info in one cell to several | Excel Discussion (Misc queries) |