Range question...

[Agenth22]

I have the following code:

Dim PasteRng As Range
On Error Resume Next
Set PasteRng = Application.InputBox("What cell do you want your data in?", Type:=8)
If PasteRng Is Nothing Then Exit Sub

It returns a range object, "PasteRng".

If i declare a variable: Dim Variable1 as Range, how do i set Variable1 equal to PasteRng offset by lets say 1 cell?

I tried a bunch of different things and nothing seems to work. I've tried:

Dim Variable1 As Range
Variable1 = Cells(PasteRng.Row + 1, PasteRng.Column)
'& 
Variable1 = PasteRng.offset(1,0)
msgbox(Variable1.address)

and some others that i have since deleted. Does anyone have any suggestions?

Thanks!!

 

[erik.van.geit]

Hi, Agenth22,

try this

Sub test()
Dim PasteRng As Range
On Error Resume Next
Set PasteRng = Application.InputBox("What cell do you want your data in?", Type:=8)
If PasteRng Is Nothing Then Exit Sub

Dim Variable1 As Range
Set Variable1 = PasteRng.Offset(1, 0)
PasteRng.Interior.ColorIndex = 6
Variable1.Select
End Sub

kind regards,
Erik

 

[XLGibbs]

why do you need to do this?

why not just type a different cell number in the input box?

Alternatively, this will work

Set Variable1 = PasteRng.Offset(1,0)

Just like you are setting the PasteRng object, you must also "Set" the variable1 Range object.

 

[Agenth22]

Eric.van.Geit and Gibbs, thanks a bunch for your help. I would have spent another couple hours trying to figure that out if one of you didn't chime in.

why do you need to do this?

why not just type a different cell number in the input box?

That would seem like the obvious thing to do. However, the user will select a cell. that cell will be where 2 columns of data will be placed with a header. I need a way of telling the program to offset data from the header by a cell or 2.

Thanks again!