converting Lotus 123 macro to Excell macro

[Guest]

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

 

[Don Guillett]

{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

 

[Dave Peterson]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Don Guillett]

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

 

[Dave Peterson]

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.