Range Selection

[cosmarchy]

Hi,

I have a function which takes the worksheet target cell as an argument and I need to dynamically select a range of cells.

Basically I need to return a range of cells which starts one column to the right of target and is three columns wide.

For example, if target pointed to A1 then I need a range of cells from B3 to D3.

So far I have this:

TargetCell.Offset(0, 1).Resize(0, 3)

which rather unhelpfully tells me there is an 'Application-defined or object-defined error'

Can anyone help?

Thanks

 

[mikerickson]

Is TargetCell a range variable that you have declared?
Excel sometimes uses an built-in variable Target. There is also ActiveCell.

Also, you are asking it to make a range that is 0 rows high. Perhaps

ActiveCell.Offset(0, 1).Resize(1, 3).Select

is close to the syntax you want.

 

[p45cal]

Not tested, but I think you'll have probelms setting a range size to 0 rows.
Try losing the 0:

TargetCell.Offset(0, 1).Resize(, 3)

or making it a 1 if you want it to be only 1 row high:
TargetCell.Offset(0, 1).Resize(1, 3)

 

[cosmarchy]

ah, I see now...

Many thanks guys