Is there any way to make Excel macros less stupid?

January 5, 2005 8:33 AM

Is there any way to make Excel macros less stupid? [MI]

So say for instance I want a macro that will, among other things, copy the contents of the current cell and paste them in the next. If I try to record such a macro, and the cell I start with contains "chickens", the macro will cheerfully paste "chickens" in the next cell even if I run it from a cell which contains "velociraptors". Clearly this is suboptimal.

I'm fully willing to learn visual basic if it'll do the sorts of things I want it to, I just want to know if it will before I waste any effort on it.

posted by squidlarkin to Computers & Internet (15 answers total)

It totally will. I forget the Excel-specific syntax, off-hand, to refer to the current contents of a cell in your code, as opposed to a literal value, but you can definitely do that in Excel VB.
posted by LairBob at 8:38 AM on January 5, 2005

i'm not a programmer..... but I found a program recently that is pretty easy to use/learn - AutoIt. I wanted to automate some mouse clicks.... about 500 of them. But it is fairly easy to use.... feel free to email me if you have any questions.
posted by TuxHeDoh at 8:39 AM on January 5, 2005

Something like this will copy the contents of the currently selected cell, and paste them into the adjacent cell. Works for me.

Sub copypaste()
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
End Sub
posted by buxtonbluecat at 8:53 AM on January 5, 2005

Note that you can also set the formula of a cell using VB (although again, the syntax escapes me off-hand), so if you needed something more flexible, you could have your code assign something like "=E4" as the formula, rather than directly pasting in the new value. That way, once you established that relationship, the new cell would update automatically without having to run the macro again.
posted by LairBob at 8:58 AM on January 5, 2005

Can anyone recommend an Excelent text for learning such? I bought an O'Reilly book, got something out of it, but find it's a little too advanced for a semi-beginner.
posted by ParisParamus at 9:06 AM on January 5, 2005

Response by poster: buxton's code fragment will be terribly useful, but now for a more specific problem: what if instead of the entire cell, I just wanted to copy the last five characters and append them to the contents of the next cell?
posted by squidlarkin at 9:24 AM on January 5, 2005

Response by poster: Oh, and AutoIt sounds like something I've often longed for, many thanks!
posted by squidlarkin at 9:27 AM on January 5, 2005

Regarding your last question, then you don't want to use "copy and paste"--you want to do something along these lines:

tempVariable = OldCell.Value
tempVariable = Right(tempVariable, 5)
NewCell.Value = NewCell.Value + tempVariable

(Note, that's all "p-code", to get the idea across, not legal VB syntax.)
posted by LairBob at 9:43 AM on January 5, 2005

copy the last five characters and append them to the contents of the next cell

To copy the last five characters of A1, and append to B1, putting the results in C1, put the following formula in cell C1:

=RIGHT(A1,5) & B1

I realize this isn't a macro, but sometimes a formula is as useful. (Apologies if this formula was obvious.)
posted by WestCoaster at 9:50 AM on January 5, 2005

PP:

Anything by this guy is worth the $ you'd spend.

He has an interesting website too.
posted by xena at 10:18 AM on January 5, 2005

Response by poster: Ok, last question. I'm poring over the VB help file and I simply can't find the command to just output text to a cell. Except for Paste, which only works with the clipboard. Grr.
posted by squidlarkin at 10:26 AM on January 5, 2005

You have to assign your text string to the ".Value" property of the cell.
posted by LairBob at 10:56 AM on January 5, 2005

Response by poster: Excellent. Now I can clone my velociraptors with ease. Thanks all!
posted by squidlarkin at 12:09 PM on January 5, 2005

Here is your macro:

ActiveCell.Copy Destination:=ActiveCell.Offset(0, 1)

That's it.
posted by bingo at 9:16 PM on January 5, 2005

...that is, in relation to the original question, not the follow-up. Notice that my macro doesn't select anything. Selecting in Excel VB code is conducive to slowness. You may not notice it if you are running only one small macro, but let things get a bit more complicated, and you will.
posted by bingo at 9:24 PM on January 5, 2005