Macro offset using the value in a cell
 

Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to B5).

I know offset ( , ) should be used for this, but as a novice I don't seem to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

Macro offset using the value in a cell
 

You didn't post your macro so far

Sub copydestbasedoncell()
Range("c13").Copy Range("c13").Offset(Range("a1"))
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"StephenT" wrote in message
...
Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two
rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to
B5).

I know offset ( , ) should be used for this, but as a novice I don't seem
to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

Macro offset using the value in a cell
 

Hi,

try this

Range("A1").Offset(Range("A1").Value - 1, 1).Value = Range("A1").Value

so if A1 contains a 4, that value is copied to B4

Mike

"StephenT" wrote:

Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to B5).

I know offset ( , ) should be used for this, but as a novice I don't seem to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

Macro offset using the value in a cell
 

with activesheet
.range("B1").copy _
destination:=.range("b1").offset(.range("a1").valu e,0)
end with

There are no validity checks here. If you have a number too small (negative) or
too large or even text in A1, then it'll blow up.

StephenT wrote:

Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to B5).

I know offset ( , ) should be used for this, but as a novice I don't seem to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

--

Dave Peterson