set cell range coordinates to variables

[safetyrs]

Why doesn't the red line work? I'm trying to set cell range coordinates to variables for easier maintenance in the future in case rows/columns shift using VBA in Excel. This way I can change the variable values in one place instead of scattered throughout the code. There is a lot more to all of this but that is the line I'm having trouble with, I eventually copy the range data to another sheet in specific cells different.

    Dim CellsDown1 as Integer, CellsAcross2a as Integer
    Dim CellsAcross2b as Integer
    Dim Rng2Data as Range

    CellsDown1 = 13
    CellsAcross2a = 5
    CellsAcross2b = 10
   
[b][COLOR=red]Set Rng2Data = Sheets("Data").Range(Cells(CellsDown1, CellsAcross2a), _ Cells(CellsDown1, CellsAcross2b))[/color][/b]

I can do one cell using

Set Rng1Data = Sheets("Data").Cells(CellsDown1, CellsAcross1a)

but I would like to copy a whole range.

 

[Rofeu]

Hi,

what's the error you are getting?

Try this:

Set Rng2Data = Sheets("Data").Range(Cells(CellsDown1, CellsAcross2a).Address & ":" & Cells(CellsDown1, CellsAcross2b).Address)

Cheers,

Roel

 

[SkipVought]

Hi,

Your code works perfectly.

What do YOU think is not working?

Skip,

_[red][/red]

 

[safetyrs]

Getting Runtime error 1004: Application-defined or object-defined error.

Roel, I used your line of code and it worked perfectly. Thanks a bunch. I've been banging my head over this because it appears that it should have worked but just wouldn't.

Doing this will save me a lot of time as opposed to hard coding the coordinates.

 

[Rofeu]

I've had that problem before. Some computers would execute your line of code without issue while on others it failed.

I never found out what causes it, but my workaround has always worked.

Cheers,

Roel

 

[xlbo]

You need to qualify the cells reference with the sheet:

Set Rng2Data = Sheets("Data").Range(Sheets("Data").Cells(CellsDown1, CellsAcross2a), Sheets("Data").Cells(CellsDown1, CellsAcross2b))

or

Set Rng2Data = Range(Sheets("Data").Cells(CellsDown1, CellsAcross2a), Sheets("Data").Cells(CellsDown1, CellsAcross2b))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question