Forget about it:

Range(FirstMonth, LastMonth.Offset(0, 2)).Select

"ewan7279" wrote:

Hi all,

I am struggling with the correct syntax for the above. I am writing a macro
to create a chart from a list of monthly data. I want the user to be able to
select the first and last months (will usually be 12 months apart). I have
got as far as finding the first and last variables, but can't work out how to
select the cells from one to the other:

'Financials
Dim Message, Title, Default, MyValue
Message = "Enter first month" ' Set prompt.
Title = "First Month" ' Set title.
Default = "Jun-06" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

Dim FirstMonth, LastMonth, chartdata As Range
Dim ws As Worksheet
With Worksheets("Gas").Range("a:a")
Set FirstMonth = .Find(MyValue, LookIn:=xlValues)
If Not FirstMonth Is Nothing Then
MsgBox FirstMonth
FirstMonth.Select
Else
MsgBox "Value not available", vbOKOnly, "Error"
Exit Sub
End If
End With

Message = "Enter last month" ' Set prompt.
Title = "Last Month" ' Set title.
Default = "Jun-07" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

With Worksheets("Gas").Range("a:a")
Set LastMonth = .Find(MyValue, LookIn:=xlValues)
If Not LastMonth Is Nothing Then
'Last required cell in range
LastMonth.Offset(0, 2).Select
'This is where I want to selct from FirstMonth to
LastMonth.Offset(0, 2)
'and set the range as chartdata (I think?!)
chartdata.Select

Exit Sub
End If
End With

Please help...!!