Excel - Select Variable Cell Range

[ceecld]

What I'm looking to do is have a main excel page with ten macro buttons each importing and plotting different graphs.

For one of the options I would like the user to enter the a value (channel No.) into a cell for which they want a statistical plot.

This statistical data will have already been imported using a macro, however it will contain a number of different channels of information.

So what im wanting to do is, let say the user enters channel 2 into cell L2 then i would plot a graph of A52:B71

the formula for calculating the A cell no is: 4 + 24 * ChNo.
and for calculating B: A + 19

e.g.
ChNo. 0 => A4:A23
ChNo. 3 => A76:B95 etc.

So my question is, is it possible to vary the range depending on a cell value?

i was trying to assign a variable to the cell value then calc the topleft and bottom left cell numbers using the formula, but i cant seem to get it to work.

Thanks

Chris

 

[ceecld]

Heres a peice of my code, so as you know what im trying to do

Windows("Tx Inrush.xls").Activate
chsel = Range("L2").Value
topleft = chsel * 24 + 4
botrgt = topleft + 19

Windows("mrunout.out").Activate

Range("A&topleft.value:B&botrgt.value").Select

 

[Loomah]

Hi Chris
Something like this?

Sub mit()
Dim row1 As Long, row2 As Long
row1 = 4 + (24 * Range("L2"))
row2 = row1 + 19

Range(Cells(row1, 1), Cells(row2, 2)).Select
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Hi again!
Your second post wasn't there when I posted but it looks as if you have the solution yourself - you just need to adjust the way your range is written

ie
Range("A"& topleft.value & ":B" & botrgt.value).Select

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

Chris,

Use the OFFSET Function in Insert/Name/Define to create an X and Y range
[tt]
X: =OFFSET(Sheet2!$A$4,4+24*Sheet2!$L$2,0,20,1)
Y: =OFFSET(Sheet2!$B$4,4+24*Sheet2!$L$2,0,20,1)
[/tt]
then use X & Y in the Chart Source Data - Series tab
for x values
[tt]
=Sheet1!X
[/tt]
for y values
[tt]
=Sheet1!Y
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[ceecld]

Thanks for your fast replies, im just trying it just now. its deffinetly selecting the right cells but im not sure if its liking the selection for making a grpah from it.

Thanks again

 

[Loomah]

Clicked the Submit Post button too soon!
You should also drop the .value bit so you end up with

Range("A" & topleft & ":B" & botrgt).Select

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[ceecld]

Thanks guys! I'm just tweaking it now to get it just right.

 

[ceecld]

The problem im having now is that the XValues for the graph

ActiveChart.SeriesCollection(1).XValues = "=mrunout!R&topleft&C1:R&botrgt&C1"

(The Xvalues for the chart are the values in the A column.)

Sorry if im being stupid,

thanks again

 

[ceecld]

Ive got it now.

Thanks again for all your help