Excel Spreadsheet Macro, - How Does One Move Up/Down Relatively?

  thegreypanther 19:08 29 Sep 2013
Locked

I'm being driven nuts by a Macro I'm trying to write for a financial spreadsheet in Excel 2007.

The action I am trying to build into the Macro is to move the cursor down 1 Row, then copy a range at that point.

Problem is, the Macro puts in the Absolute address from my recorded keystrokes which completely frustrates what I am trying to do.

Any hints or tips would be highly appreciated.

  compumac 19:36 29 Sep 2013

Do you mean a macro to move the cursor down one row and to copy a determined range in that lower row and then put it to the clipboard?

  compumac 19:36 29 Sep 2013

Do you mean a macro to move the cursor down one row and to copy a determined range in that lower row and then put it to the clipboard?

  thegreypanther 20:09 29 Sep 2013

Compumac

Yes, - exactly that.

The macro as written using the "Record" method ends up by copying a row of numbers to an absolute address (e.g. "A324"). What I am trying to get the macro to do is to first go to "end.down" (works OK up to here) and THEN move to the next row down before copying the row of numbers.

Unfortunately, using the record method the macro plumps for the absolute address rather than the relative (e.g. 1 row down).

  Picklefactory 20:51 29 Sep 2013

Try this. It should select and copy from Col B to col J from the next row down. Does that help?

Sub OneRowDown() Selection.Offset(1, 0).Select Range("B" & ActiveCell.Row & ":J" & ActiveCell.Row).Copy End Sub

  compumac 20:51 29 Sep 2013

Got you.

I had to do a very similar exercise on some large spreadsheets years ago. I just had a quick look at this but will have to rack my ever-greying brain cells on this and will continue to think back.

  Picklefactory 20:52 29 Sep 2013

PS, forgot to mention, obviously just alter "B" and "J" to whatever range you wish to copy

  Picklefactory 20:54 29 Sep 2013

Hmmm. I forgot that VBA code doesn't paste well into this forum. This may be clearer.

Sub OneRowDown()

Selection.Offset(1, 0).Select

Range("B" & ActiveCell.Row & ":J" & ActiveCell.Row).Copy

End Sub

  thegreypanther 21:13 29 Sep 2013

Picklefactory

Works PERFECTLY!!!

Many thanks indeed.

  compumac 21:50 29 Sep 2013

Thanks Picklefactory -

I do not have to exercise the old grey cells after all.

  Picklefactory 13:04 30 Sep 2013

No prob, you're welcome

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?