Range question...

Agenth22

New Member
Joined
Dec 12, 2005
Messages
10
I have the following code:

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:
Code:
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!!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Agenth22,

try this
Code:
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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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

New Member
Joined
Dec 12, 2005
Messages
10
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,680
Members
412,481
Latest member
nhantam
Top