Home |
Search |
Today's Posts |
#1
|
|||
|
|||
converting Lotus 123 macro to Excell macro
I need to parse/edit one line at a time usng the same one-line macro. When I
use macro-recorder, it links to the specific cell reference. I need the below macros to be free from specific cell references - it needs to run on the current cell and move down one row. {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro and {edit}{L 10}{BS 30}{D} reads as: open cell in edit mode - move left 10 characters - backspace 30 characters - move down one line - end macro Is there a simple macro that can do these? thanx Mark |
#3
|
|||
|
|||
When you backspace 30 characters, are you wiping out all the "prefix" characters
in that cell or are you keeping some: If you have this string: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ do you end up with: abcdefghijklQRSTUVWXYZ (delete "middle" 30 characters) or QRSTUVWXYZ (keep only the last 10 characters) If you delete the middle characters: Option Explicit Sub testme() If Len(ActiveCell.Value) 40 Then ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 40) _ & Right(ActiveCell.Value, 10) Else ActiveCell.Value = Right(ActiveCell.Value, 10) End If ActiveCell.Offset(1, 0).Select End Sub If you're only keeping the final 10 characters: Option Explicit Sub testme2() ActiveCell.Value = Right(ActiveCell.Value, 10) ActiveCell.Offset(1, 0).Select End Sub mark h wrote: I need to parse/edit one line at a time usng the same one-line macro. When I use macro-recorder, it links to the specific cell reference. I need the below macros to be free from specific cell references - it needs to run on the current cell and move down one row. {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro and {edit}{L 10}{BS 30}{D} reads as: open cell in edit mode - move left 10 characters - backspace 30 characters - move down one line - end macro Is there a simple macro that can do these? thanx Mark -- Dave Peterson |
#4
|
|||
|
|||
First of all - thank you for your assistance.
how do I move the cursur to the first character on the row before deleting the seven characters? ex: abcdefghijklmnopqrstuvwxyz result should be: ghijklmnopqrstuvwxyz As I tried your suggestion, the result was to delete the alst 7 characters, instead of the first 7. "Don Guillett" wrote: {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro activecell=left(activecell,len(activecell)-7)) activecell.offset(1).select However all of this can be done much quicker for all cells WITHOUT any selections. {edit}{L 10}{BS 30}{D} Don't understand what is happening with the second macro since it appears to do nothing -- Don Guillett SalesAid Software "mark h" wrote in message ... I need to parse/edit one line at a time usng the same one-line macro. When I use macro-recorder, it links to the specific cell reference. I need the below macros to be free from specific cell references - it needs to run on the current cell and move down one row. {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro and {edit}{L 10}{BS 30}{D} reads as: open cell in edit mode - move left 10 characters - backspace 30 characters - move down one line - end macro Is there a simple macro that can do these? thanx Mark |
#5
|
|||
|
|||
First of all - thank you for your assistance.
I have some follow up questions: why does your suggestion test the length of the string for 40 characters? The first suggestion was where I was going (deleting the middle 30 characters). I am not following how the macro moves the cursor position and then deletes the 30 characters. I had no problem with macro's in 123 - for some reason, I am missing it in Excell. thanx again. "Dave Peterson" wrote: When you backspace 30 characters, are you wiping out all the "prefix" characters in that cell or are you keeping some: If you have this string: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ do you end up with: abcdefghijklQRSTUVWXYZ (delete "middle" 30 characters) or QRSTUVWXYZ (keep only the last 10 characters) If you delete the middle characters: Option Explicit Sub testme() If Len(ActiveCell.Value) 40 Then ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 40) _ & Right(ActiveCell.Value, 10) Else ActiveCell.Value = Right(ActiveCell.Value, 10) End If ActiveCell.Offset(1, 0).Select End Sub If you're only keeping the final 10 characters: Option Explicit Sub testme2() ActiveCell.Value = Right(ActiveCell.Value, 10) ActiveCell.Offset(1, 0).Select End Sub mark h wrote: I need to parse/edit one line at a time usng the same one-line macro. When I use macro-recorder, it links to the specific cell reference. I need the below macros to be free from specific cell references - it needs to run on the current cell and move down one row. {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro and {edit}{L 10}{BS 30}{D} reads as: open cell in edit mode - move left 10 characters - backspace 30 characters - move down one line - end macro Is there a simple macro that can do these? thanx Mark -- Dave Peterson |
#6
|
|||
|
|||
If you have a list or only one cell this will work. Just highlight the list
and fire this macro. The old lotus EDIT method is NOT necessary. The on error statement will ignore those with less than 7 to remove. BTW 7 would also remove the g in the example Sub delete1st7() For Each c In Selection On Error Resume Next c.value = Right(c, Len(c) - 7) Next End Sub -- Don Guillett SalesAid Software "mark h" wrote in message ... First of all - thank you for your assistance. how do I move the cursur to the first character on the row before deleting the seven characters? ex: abcdefghijklmnopqrstuvwxyz result should be: ghijklmnopqrstuvwxyz As I tried your suggestion, the result was to delete the alst 7 characters, instead of the first 7. "Don Guillett" wrote: {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro activecell=left(activecell,len(activecell)-7)) activecell.offset(1).select However all of this can be done much quicker for all cells WITHOUT any selections. {edit}{L 10}{BS 30}{D} Don't understand what is happening with the second macro since it appears to do nothing -- Don Guillett SalesAid Software "mark h" wrote in message ... I need to parse/edit one line at a time usng the same one-line macro. When I use macro-recorder, it links to the specific cell reference. I need the below macros to be free from specific cell references - it needs to run on the current cell and move down one row. {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro and {edit}{L 10}{BS 30}{D} reads as: open cell in edit mode - move left 10 characters - backspace 30 characters - move down one line - end macro Is there a simple macro that can do these? thanx Mark |
#7
|
|||
|
|||
If you want to delete 30 characters and keep 10, the I would think that the
length of the string should be at least 40 characters. And the macro doesn't move the cursor in the cell being changed. It just strips out the middle characters. Your Lotus 123 macro did keystroke by keystroke changes. The excel macro will accomplish the same thing by using the string and some VBA functions. mark h wrote: First of all - thank you for your assistance. I have some follow up questions: why does your suggestion test the length of the string for 40 characters? The first suggestion was where I was going (deleting the middle 30 characters). I am not following how the macro moves the cursor position and then deletes the 30 characters. I had no problem with macro's in 123 - for some reason, I am missing it in Excell. thanx again. "Dave Peterson" wrote: When you backspace 30 characters, are you wiping out all the "prefix" characters in that cell or are you keeping some: If you have this string: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ do you end up with: abcdefghijklQRSTUVWXYZ (delete "middle" 30 characters) or QRSTUVWXYZ (keep only the last 10 characters) If you delete the middle characters: Option Explicit Sub testme() If Len(ActiveCell.Value) 40 Then ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 40) _ & Right(ActiveCell.Value, 10) Else ActiveCell.Value = Right(ActiveCell.Value, 10) End If ActiveCell.Offset(1, 0).Select End Sub If you're only keeping the final 10 characters: Option Explicit Sub testme2() ActiveCell.Value = Right(ActiveCell.Value, 10) ActiveCell.Offset(1, 0).Select End Sub mark h wrote: I need to parse/edit one line at a time usng the same one-line macro. When I use macro-recorder, it links to the specific cell reference. I need the below macros to be free from specific cell references - it needs to run on the current cell and move down one row. {edit}{home}{Del 7}{d} reads as: open cell in edit mode - move to the home position of the row - delete seven characters - move down one line - end macro and {edit}{L 10}{BS 30}{D} reads as: open cell in edit mode - move left 10 characters - backspace 30 characters - move down one line - end macro Is there a simple macro that can do these? thanx Mark -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File size too big after converting Lotus 1-2-3 to Excel | Excel Discussion (Misc queries) | |||
Converting from Lotus 123 to Excel | Excel Discussion (Misc queries) | |||
Converting Lotus Spreadsheet to Excel | Excel Discussion (Misc queries) | |||
Converting Lotus 123 to Excel | Excel Discussion (Misc queries) | |||
Macro Text into Excell | Excel Discussion (Misc queries) |