selecting a cell relative to activecell in an excel macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I hope some-one can help with this problem:
I am trying to write a macro to do the following:
1) insert a row
2) copy the cell which is right 3 and up 1 from column A in the inserted row
3) paste it to the cell which is down 5 and left 1 from the copied cell
there seems to be a problem with the syntax for the second step:
ActiveCell.Cells(4, -2).Activate
also tried:
ActiveCell.Cells(4, -2).Select
I suspect the problem lies with the negative number used to denote relative movement up, but I have no idea how else to denote this...
 
Use the offset property to achieve this.
In your example, assuming you have the correct cell
selected:

activecell.offset(5,-1).value=activecell.offset(-1,3).value

OR if you really want to copy the cell rather than just
the value:

activecell.offset(-1,3).copy destination:=activecell.offset
(5,-1)

Cheers, Pete.
-----Original Message-----
Hi,
I hope some-one can help with this problem:
I am trying to write a macro to do the following:
1) insert a row
2) copy the cell which is right 3 and up 1 from column A in the inserted row
3) paste it to the cell which is down 5 and left 1 from the copied cell
there seems to be a problem with the syntax for the second step:
ActiveCell.Cells(4, -2).Activate
also tried:
ActiveCell.Cells(4, -2).Select
I suspect the problem lies with the negative number used
Click to expand...
to denote relative movement up, but I have no idea how
else to denote this...
 
Back
Top