Conditional actions
 

Hi,
What I am attempting to do is make a cut and paste in a macro with a
variable length that is determined by a number or adress listed in a cell. Is
this possible using the INDIRECT reference?

Conditional actions
 

In a macro, there is no need for the INDIRECT function.

Let's say that the row number you want to copy through is in cell A1. Then
use:
Range("B2:B" & Range("A1").Value).Copy

If it is a cell address in cell A1, then use:
Range("B2:" & Range("A1").Value).Copy

If it is a range address in cell A1, then use:
Range(Range("A1").Value).Copy

And quite likely, you can calculate the value without using the extra cell.
But your description lacks clues as to what you are doing.

HTH,
Bernie
MS Excel MVP


"Chris Manning" wrote in message
...
Hi,
What I am attempting to do is make a cut and paste in a macro with a
variable length that is determined by a number or adress listed in a cell.
Is
this possible using the INDIRECT reference?

Conditional actions
 

Bernie-
thanks for the tip. More specifically, what I am trying to do is have a user
input a data set of variable length, and then have a VBA GUI pop up and the
user will input how long the data table is, and then excel will enter that
data into a cell, and an equation in another cell will then convert that to
the addresses of the start and end of the data set, and then the macro will
copy and paste that data set to other worksheets. Is the correct and easiest
method for this the Range function that you listed?

"Bernie Deitrick" wrote:

In a macro, there is no need for the INDIRECT function.

Let's say that the row number you want to copy through is in cell A1. Then
use:
Range("B2:B" & Range("A1").Value).Copy

If it is a cell address in cell A1, then use:
Range("B2:" & Range("A1").Value).Copy

If it is a range address in cell A1, then use:
Range(Range("A1").Value).Copy

And quite likely, you can calculate the value without using the extra cell.
But your description lacks clues as to what you are doing.

HTH,
Bernie
MS Excel MVP


"Chris Manning" wrote in message
...
Hi,
What I am attempting to do is make a cut and paste in a macro with a
variable length that is determined by a number or adress listed in a cell.
Is
this possible using the INDIRECT reference?