turning absolute references into relative (macro recorder)

anxoperez

Active Member
Joined
Oct 3, 2007
Messages
254
I suppose the answer is Not, but I'll ask it anyway.

Whenever I use the macro recorder to record a macro, all the references to a cell are absolute and not relative. If I am in A1 and I move to A2, it writes

Range("A2").Select

But for a programming-illiterate such as me, it would be terribly useful if instead it said:

ActiveCell.Offset(1, 0).Select

Is there any way for the macro recorder to provide that kind of code? Any other alternative sources?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Probably not!!!
That said, the macro recorder is notorious for selecting ranges when almost always it is not necessary. Why not post a walk-thru of what you are recording?


lenze
 

anxoperez

Active Member
Joined
Oct 3, 2007
Messages
254
Ok. I'll try because there is no other option, but it really would be amazing if it could.

Someone should propose it! ;)

Here it goes.

My active cell is in column BA. All previous columns have contents (are not empty) and several of the following columns are also not empty, then there is a large gap (about 30 empty columns), and then, in FU, contents start again.

The first row of the non-empty columns is a header row and therefore not empty (meaning that the code

Selection.End(xlRight).Select

or
Selection.End(xlLeft).Select

can be used)


What I need to do is this:

1- select the contents form BB2 downward (they would be adjacent, so no empty cells) and paste them in BA1.
2- Select BA (either the whole column or the contents)
3- paste it on the first empty cell before FW1
4- Return to the column I was before (in this case BA) and delete all of BA and BB except the headers.

Of course I need for this to take place in whatever column I am in, so the references would have to center not around BA but around the column of the active cell.

here is the code I obtained from the macro recorder, which of course lacks the "relative references" I was looking for.

Also, I did not know how to make it go back to the column I was working in, so the part about going back and deleting the contents is missing.

Range("BB2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlUp).Select
ActiveCell.Offset(1, -1).Select
ActiveSheet.Paste
Range("BA1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range("FV1").Select
ActiveSheet.Paste
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top