macro not working right

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to cut the last name from a cell and paste it to the adjacent cell. In the past, I have been able to automate this action (on a different system, different job).
I recorded the macro thus, starting in the cell with first and last name: F2 > ctrl shift right arrow > ctrl x > enter (move right) > ctrl v > down arrow > left arrow > stop recording [relative reference is on]. Instead of performing the desired action on the next line/cell, it duplicates the line above and does so ad nauseum if I activate the macro.
Here's the coding
Sub name(

' name Macr
' Macro recorded 3/2/2004 by J. S

' Keyboard Shortcut: Ctrl+

ActiveCell.FormulaR1C1 = "John J
ActiveCell.Offset(0, 1).Range("A1").Selec
ActiveSheet.Past
ActiveCell.Offset(1, -1).Range("A1").Selec
End Su

Can someone help me fix this macro? Thanks in advance if you can.
 
One way:

This uses the TextToColumns function (Data/Text to Columns):

Public Sub Split2Names()
ActiveCell.TextToColumns _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
End Sub


jeanette s. said:
I want to cut the last name from a cell and paste it to the adjacent cell.
In the past, I have been able to automate this action (on a different system,
different job).
I recorded the macro thus, starting in the cell with first and last name: F2
ctrl shift right arrow > ctrl x > enter (move right) > ctrl v > down arrow
left arrow > stop recording [relative reference is on]. Instead of
Click to expand...
performing the desired action on the next line/cell, it duplicates the line
above and does so ad nauseum if I activate the macro.
Here's the coding:
Sub name()
'
' name Macro
' Macro recorded 3/2/2004 by J. S.
'
' Keyboard Shortcut: Ctrl+m
'
ActiveCell.FormulaR1C1 = "John J "
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub

Can someone help me fix this macro? Thanks in advance if you can.
Click to expand...
 
It's difficult to give suggestions if you don't explain what "did not
work" means.

What is the macro not doing that you expect it to? What is the macro
doing that you didn't expect?

Did it do nothing? the wrong thing?

What does your data look like in the active cell when you run the macro?
What should it look like when the macro's done?
 
Jeanette:

Let's look at your recorded routine and Jim McGimpsey's.

They start from ActiveCell: that's the last one you clicked.
If you don't click another, it doesn't look as though your routine wil
know you want to move down the list. When you say "it didn't" work
did it do one and leave the other hundreds untouched?

Can you manage to turn this into a loop so that it starts with th
active cell being the one you wish first to operate on and progresse
to the last one you want to deal with and then gracefully stops?

Al
 
Sorry I wasn’t more clear. When I say it didn’t work, I mean that in trying to “run†your macro (what I copied from this site and pasted in place Visual Basic module in place of what was there), the debugger is opened

Col “A†Col “Bâ€
Stephanie Buchner <--what it looks like before macr
Stephanie Buchner <--what I want it to look like after macr

I am more than willing to run (prefer hotkey) the macro for each line, not loop it.
(This was easier with older versions.
 
Jeanette:

Well ... I can categorically say that Jim McGimpsey's routine works for
me. So what are we doing that is different?

At the risk of being boring, offensive or any old thing I'll tell you
what I did. Then you can decide whether you are doing the same and
hence whether there is something else in play.

Copy JM's routine onto the code sheet of the worksheet containing the
names.
Put a cmd_button on the worksheet.
Link it to the JM routine.
Select a name on the worksheet than click the cmd_button.
The name separates, leaving part where it was and the other part in the
next column.
Oh - and John Philip Souza ends up in three columns.
Honest!

Alf

:) :)
 
Back
Top