turning absolute references into relative (macro recorder)

[anxoperez]

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?

 

[lenze]

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]

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