creating macro

[Jeff Roe]

I know very little about VB in Excel but I've gone through the "Record New
Macro" to copy several different fields in different rows to a single row
and several columns.

The problem I have is the macro always runs against the same data in the
same row and cells and copies to the same row and cells i.e. absolute
location.

I want to run the macro through the same sequence but relative to where my
selected cell is.

In other words instead of the absolute -Range("D19").Select- VB statement
how do I make it relative?

Thanks

Jeff...

 

[David McRitchie]

Hi Jeff,
A bit sparse on what you want to do, and we don't really
want to look at a big macro either.

Recording a macro is seldom going to provide you with code
that you can directly use. Rather you generally record a
macro to get an idea of what instructions you need and how
to use them, but you are not going to see generated loops
in a recorded macro.

But there are two things you have when you start a macro
activecell
selection can consists of multiple rectangular ranges and/or cells

The next thing to consider is that you want to avoid changing the
selection within the macro or anything else that would change
the view.

if you want to refer to
activecell.offset(0,1) the cell to the right of the active cell
activecell.offset(-1,0) the cell above the active cell
activecell.entirerow the entire row that the active cell is in
activecell.offset(1,0).entirerow the entire row below the active cell

I would suggest taking a look at the following web pages
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/join.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
http://www.mvps.org/dmcritchie/excel/snakecol.htm

Previous programming experience would help; otherwise,
you might try looking for some VBA online tutorials
http://www.mvps.org/dmcritchie/excel/excel.htm#vbatutorials
or buy a book.

 

[Jeff Roe]

Thanks!

I think that gets me where I want to go!

Jeff...