Offsetting Column Ranges - How?

[lynxbci]

Hi
Scenario: There is a column range predefined lets say $B:$G (6 columns)

Question 1The user enters a number in a cell (A1) and i want to offset this selection by that many columns.
eg If the users enters 3 in A1 then the range would now be $E:$J (3 columns to the right, still 6 columns wide).

How is this done?

Question 2If the user enters a number in a cell (A2) then this will redefine the columns in the range.
eg If the user enters a 4 in A2 then the range would now be $E:$H (3 cols right and now 4 cols wide).

How is this done?

I am aware of how to set ranges with tables, but don't want to use rows at all, only column references, and it is proving difficult.

Many thanks in advance

 

[Andrew Poulsom]

In VBA or via a defined name?

 

[lynxbci]

In VBA, not fussed about keeping or naming the range, just knowing what the new range is and then using it for a lookup reference.

thanks

 

[Andrew Poulsom]

Like this?

Sub Test()
    MsgBox Range("B:G").Offset(, Range("A1").Value).Address
    MsgBox Range("B:G").Offset(, Range("A1").Value).Resize(, Range("A2").Value).Address
End Sub

 

[lynxbci]

Thanks