Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a variable range | Excel Worksheet Functions | |||
Select a variable range | Excel Programming | |||
Use a Variable to select a range | Excel Discussion (Misc queries) | |||
select a variable range | Excel Programming | |||
Select a Range Through a Variable | Excel Programming |