Select variable range
 

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...!!

Select variable range
 

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...!!