Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

recording macros

Status
Not open for further replies.

falcon4

Technical User
Sep 23, 2002
42
US
I am attempting to learn recording macros.

I would like to click on any cell and execute a macro that does the following keystroke

F2, HOME, delete, delete, delete

This will remove unwanted characters from the beginning of the contents of the cell.

The instructions in excel's help file do not help me.

At what point do I start recording?
Do I need to be in a cell the macro will be used on to start the recording?
Do I move to the next cell before stop recording

In previous attempts it seems to copy the contents of the cell that was used during the recording.

I am sure this is an elementary topic that someone can help with.

Thanks,

Gary
 
Doesn't really matter where you start as it will record everything -you just need to select the bit you want
However, in this instance, you will not get much

You need
Activecell.value = right(activecell.text,len(activecell.text)-3) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
 
Hey this works great
Can you explain to me the parts of this macro?
I'm not sure what to alter, for instance if I wanted to delete just the first 2 characters

Thanks
 
No problem.
Activecell.value = right(activecell.text,len(activecell.text)-3)

Activecell refers to the current cell that is selected (on the active sheet)
We use the VALUE property to write a value OR text to a cell as the TEXT property of the cell is read only
right refers to the RIGHT function - same as in a worksheet. See help file / function editor for more details
activecell.text refers to the content of the active cell (as per above)
What the right(activecell.text,len(activecell.text)-3)
bit is doing is taking a right hand portion of the text that is in the cell but only taking the number of characters that is equal to the number of chars in the cell -3 (LEN returns the number of characters in a cell)
To change it for 2 deletions, just change the -3 to -2 Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
 
Thanks again,

When I originally tried this using the record macro function, I could not get the same results. Do you know of a reason why just recording the keystrokes would not create the same results?

Thanks

Gary
 
Yup - this is using a function - recording a macro just records exactly what you do. I did mention this in my 1st post.
You would get a similar (but not quite the same) result if you entered
=right(A1,len(a1)-3)
into a cell where your text is in A1 Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top