Macros

[SandyG]

I created a macro that would move a range of cells (ex.
B2:R2)over by one column.

Okay, that worked out just fine for that row, BUT...

I want to be able to use the same macro on other rows as
well (ex: B38:R38), but I can't figure out how to do it.

Is there a wild character I should be using along with the
row number?

Sandy

 

[SandyG]

Okay, I don't understand how to enter this information
into a Macro. Here's what I'm trying to do.

I want to move several cells within a row over one column
(example: I want to move the information in B2, C2 and D2
over one column, so D2 will be in E2, C2 will be in D2 and
B2 will be in C2).

Then, I want the Macro to perform the same function for
other rows (example: Move B3, C3 and D3 over one column,
so D3 will be in E3, C3 will be in D3 and B3 will be in
C3).

So "x1ToRight" in the first option sounds like what I
want. But how do I enter that information as a Macro
after I get to the "Recording Macro" button? I can't just
start typing, because it overwrites my data.

S.

 

[J.E. McGimpsey]

Welcome to the world of VBA programming!

It's not that difficult! Check out David McRitchie's Getting Started
With Macros page.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Most recorded macros in XL/VBA are about useless unless you edit
them - though they sometimes get you most of the way to where you
want to go.

 

[David McRitchie]

Hi Sandy,
Thanks (it was my getstarted.htm web page that was mentioned).

Many people start out with macros by recording a macro.
John already gave you a macro to do your first question.

Before starting I would highly recommend installing
MarkCells macro, to create some test data,
http://www.mvps.org/dmcritchie/excel/join.htm#markcells
so you can start with marked cells to be able to tell where
a cell was when you marked it by creating test data with
the address of the cell when the macro is run.
i.e. Select A1:W80 for instance and run the macro
(you can use the name box to supply the range)

When you move things around you will know where data
originally came from.


Now you can record a macro and modify it to make it more
generic.

Alt+F8, Macro, Record Macro

Have it go into your personal.xls (see bottom of dialog)
accept the name assigned probably Macro1

Select columns you want to move
Hold the shift key and grab the column boundary below
the gray column headers and drag to it's new location
for insertion. Stop Recording (the black square).

You will generate something like this:

Sub Macro18()
Columns("D:Q").Select
Selection.Cut
Columns("S:AF").Select
Selection.Insert Shift:=xlToRight
End Sub

which you can convert manually to something like:

Sub Move_DQ_ltof_S()
Columns("D:Q").Select
Selection.Cut
Columns("S").Select
Selection.Insert Shift:=xlToRight
End Sub

cols A:C same as original
Col D original column R
Col E:R original column D:Q
Col S:xx original column S:xx


BTW, Now that you know how to install a Macro and a User Defined
Function, the proper newsgroup for future threads would be the
excel.programming newsgroup.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm