Make macro independent of position

**Call** · 03-18-2011, 04:04 PM

Hi,

I've recorded a macro that copies a formula, pastes the value of that formula into the column to the right of it, copies that, goes to the worksheet to the left, brings up the Goto dialogue box, and writes B"CTRL+V"

As you can see from the last line in this code, the last part doesn't work. It always goes to row 31, column C.

How can I change that?

    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Previous.Select
    Application.Goto Reference:="R31C1"

**Leith Ross** · 03-18-2011, 05:44 PM

Hello Call,

This line of code is responsible for taking you to "C31"...

    Application.Goto Reference:="R31C1"

**Call** · 03-18-2011, 06:20 PM

Thanks for your replies

Oh, yes. I'm sorry. Reading my post back now I see I've made myself not clear at all.

I have two worksheets I'd like to match up the rows of. (Add rows in the shorter list)

To do that I've set up a 3rd worksheets which compares them. When the comparison returns FALSE, I'd like to get the current row, switch to the relevant worksheet, and go to the relevant row.

So, I've added a column with =row()... and for the rest see my first post.

I've tried your code a bit. But I haven't yet gotten it to work. I also need to go to a different worksheet

ActiveSheet.Previous.Select

------------------------------------------------------------------------------------
EDIT1:

By the way, I am able to compare the two worksheet from a technique someone on this forum taught me a while ago: =INDIRECT("'[Test.xls]Sheet 1'!D"&ROW())

The third worksheet is basically two columns that reference the two worksheets via this INDIRECT method, and a third column that has =EXACT(A3,B3) (comparing the two). I've now added the =ROW() column.

The INDIRECT method references the cells I need to jump to. Maybe that can be part of an easy solution?

--------------------------------------------------------------------------------------
EDIT2:

When pasting into the GoTo dialogue box, Excel pastes the value, so I can simplify this faulty code to:

    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Previous.Select
    Application.Goto Reference:="R9C1"

I've tried using 6StringJazzer's code, but my VBA skills are nonexistent.

**Call** · 03-19-2011, 11:04 AM

Anyone any idea?

I run into this more often: whenever a pop-up is a part of the macro, the result of the pop-up is fixed.

Normally I then just make two macros: 1 that leads up to the pop-up and a second that continues from there. I'd like to do things differently here.

**6StringJazzer** · 03-18-2011, 05:52 PM

I am trying to figure out what you really want to do, and this is how it sounds:

You have a formula in a cell. You want to paste the result of that formula in the cell immediately to the right. That value is a cell reference. Then you want to go to whatever cell that reference indicates. Is that right?

Your code suffers from macro recorder bloat. Excel's macro recorder records every step of every action, because it doesn't know what you're going to do next. Try this:

    ActiveCell.Offset(0, 1) = ActiveCell.Value
    Range(ActiveCell.Value).Activate

If you don't really need to copy the value to the cell to the right, and you're only doing that so you could copy the value from there to be able to paste it to a GoTo, then your code collapses to this: