How can I use a variable as cell address?

[Matt]

Guys,

I am still trying to make a chart with a macro but the data range
changes within one column.

What I have is column B with values but they start and end differently
each time.

Now I am thinking I could use one of the functions that detects the
first and last used cell and write that cell into a different cell.

Then I just need to make a chart from the start and end thaat is in
lets say AA1 and AA2.

BUT I dont know how I can reference that the chart is built from the
start and end cell that is in a different cell..

Any help appreciated. Programming isnt my thing (as you may have
noticed)

Matt

[tkaplan]

i'm not sure what exactly you are trying to do. can you please explain with sample data?

[K Dales]

Easy steps:
1) Record the macro where you create the chart for the data range. Don't
worry yet about the fact that the range may change, just get the basic macro.
2) You will probably see a line like this in the macro:
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C2:D4"),
PlotBy:= xlColumns
Some of the parameters may be different for your chart, but note that the
data range I selected, C2:D4, is specified in here.
3) Replace this with the calculated range. The easiest way to do this is to
use the CurrentRegion function, but it will only work if your range is a
continuous rectangular range surrounded by blank cells:
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("C2").CurrentRegion, PlotBy:= xlColumns

If you can't use the CurrentRegion, you can use other ways to find the end
of your data range - look in help under SpecialCells or End for some other
ideas.
--
- K Dales


"Matt" wrote:

> Guys,
>
> I am still trying to make a chart with a macro but the data range
> changes within one column.
>
> What I have is column B with values but they start and end differently
> each time.
>
> Now I am thinking I could use one of the functions that detects the
> first and last used cell and write that cell into a different cell.
>
> Then I just need to make a chart from the start and end thaat is in
> lets say AA1 and AA2.
>
> BUT I dont know how I can reference that the chart is built from the
> start and end cell that is in a different cell..
>
> Any help appreciated. Programming isnt my thing (as you may have
> noticed)
>
> Matt
>
>

[Matt]

Interesting

In my case the start cell (C2) also changes, not just the end cell. Can
this be done as well?

Actually what I have is values in column B. The macro then copies a
formula in Column C that will copy the values from B to C IF they fall
in a date range. The lines that dont fall in the date range only have
the formula but no value. I could fix that by a past special which
will only leave values where they should be...

Then I just need to find the start and use your formula for the end...

any recommendations for that?

Matt

[K Dales]

You can probably use the Range.End method - this is just like when you press
the end key while working in the worksheet. Range("C1").End(xlDown) acts
like putting your cursor in C1 and then pressing End followed by the down
arrow, but it is a little tricky:
If C1 has something in it, and so does C2: The resulting range is the cell
above the first blank cell in column C
If C1 has something in it but C2 does not: the resulting range is the first
non-blank cell below C1
If C1 is blank: the resulting range is the first non-blank cell in column C
So how to use this in your code would depend on if there is ever anything in
C1, and would C1 be part of your data range or just a header? If you can
give some info on what the data range in C is like I can try to get back with
a more specific answer. I would need to know:
- What is the first possible row where the data can start?
- Do you use C1 as a header for your data points?
- Can there be blank cells in the middle of your data? E.g. data is in
C2:C8 but C3 and C5 are blank.
- Is there anything else in Column C on your sheet? Any other cells filled
beneath where your graph data is?
--
- K Dales


"Matt" wrote:

> Interesting
>
> In my case the start cell (C2) also changes, not just the end cell. Can
> this be done as well?
>
> Actually what I have is values in column B. The macro then copies a
> formula in Column C that will copy the values from B to C IF they fall
> in a date range. The lines that dont fall in the date range only have
> the formula but no value. I could fix that by a past special which
> will only leave values where they should be...
>
> Then I just need to find the start and use your formula for the end...
>
> any recommendations for that?
>
> Matt
>
>

[Gord Dibben]

Matt

Have a look at Jon Peltier's site on dynamic charts.

http://peltiertech.com/Excel/Charts/index.html#hdrDyno

Also Tushar Mehta's site.

http://www.tushar-mehta.com/excel/ne...rts/index.html


Gord Dibben Excel MVP

On 27 Sep 2005 09:52:45 -0700, "Matt" <[email protected]> wrote:

>Guys,
>
>I am still trying to make a chart with a macro but the data range
>changes within one column.
>
>What I have is column B with values but they start and end differently
>each time.
>
>Now I am thinking I could use one of the functions that detects the
>first and last used cell and write that cell into a different cell.
>
>Then I just need to make a chart from the start and end thaat is in
>lets say AA1 and AA2.
>
>BUT I dont know how I can reference that the chart is built from the
>start and end cell that is in a different cell..
>
>Any help appreciated. Programming isnt my thing (as you may have
>noticed)
>
>Matt

[Matt]

Ok here it is:


Regular link (for all web browsers):
http://s19.yousendit.com/d.aspx?id=2...G3OUS977L3UZ8P

Couldnt figure out how to post data so the above link is a screen shot.

I have Endless data in Row A (Date/Time) and Temperature in Row B.

I have a macro which queries the user for a start date and time and
then copies the temperature and time that fall in the querioed range
into columns D and E. Precisely, it copies a formula in those rows
which show the value if its in the queried range.

I think a simple copy -> paste special would do away with the formulas
and only leave the values.

THEN, I need to chart this data. X axis has date/time. Y axis has
temperature.

The start and end point as well as amount of data points changes each
time so the macro has to select that itself...

Cant figure it out

Matt

[Matt]

> You can probably use the Range.End method - this is just like when you press
> the end key while working in the worksheet. Range("C1").End(xlDown) acts
> like putting your cursor in C1 and then pressing End followed by the down
> arrow, but it is a little tricky:

I cant get it to work, it gives a compile error ... (VBA dummie writing
here


> If C1 has something in it, and so does C2: The resulting range is the cell
> above the first blank cell in column C
> If C1 has something in it but C2 does not: the resulting range is the first
> non-blank cell below C1
> If C1 is blank: the resulting range is the first non-blank cell in column C
> So how to use this in your code would depend on if there is ever anything in
> C1, and would C1 be part of your data range or just a header? If you can
> give some info on what the data range in C is like I can try to get back with
> a more specific answer. I would need to know:
> - What is the first possible row where the data can start?

It could start somewhere from row 5 down and end down to row 65000.

> - Do you use C1 as a header for your data points?

No. Headers at this point are the icing ... they would be in A4 and B4

> - Can there be blank cells in the middle of your data? E.g. data is in
> C2:C8 but C3 and C5 are blank.

No. From where the data starts its continous to the end

> - Is there anything else in Column C on your sheet? Any other cells filled
> beneath where your graph data is?

No. its just the data until it ends

Thanks for your help

Matt

[K Dales]

Matt:
Looking things over now, I have a better idea of how to designate your graph
range.
There is really no need to copy the cells if you can find your graph data
range in columns A and B; after all they are the same numbers in the same
order. So let's say you have the start date/time for your date range in cell
A1 on the Results sheet and the end date/time is in B1 of Results:

Dim DateCells as Range, CheckCell as Range, SelectedCells as Range
' I will set up a variable that contains just the A column of your data (the
cells we want to check against the specified dates)
Set DateCells = Range("A1").CurrentRegion.Columns(1)
' Now I want to step through each cell in column A:
For Each CheckCell in DateCells.Cells
' Now look for a value in the specified date range:
If (Int(CheckCell.Value)>=Sheets("Results").Range("A1").Value) _
And (Int(CheckCell.Value)<=Sheets("Results").Range("B1").Value)Then
' If it meets the test, add the data row to the selected cells range
If SelectedCells Is Nothing Then ' this is needed to get started
Set SelectedCells = CheckCell.Range("A1:B1")
Else
' Add the new row to SelectedCells:
SelectedCells = Union(SelectedCells, CheckCell.Range("A1:B1")
End If
End If
Next CheckCell

This code should create a range variable SelectedCells that will contain the
graph data range. You could put this at the top of your macro and then when
it comes time to make the graph:
ActiveChart.SetSourceData Source:=SelectedCells, PlotBy:= xlColumns

I have not tested/debugged this but I think it should work.

--
- K Dales


"Matt" wrote:

> Ok here it is:
>
>
> Regular link (for all web browsers):
> http://s19.yousendit.com/d.aspx?id=2...G3OUS977L3UZ8P
>
> Couldnt figure out how to post data so the above link is a screen shot.
>
> I have Endless data in Row A (Date/Time) and Temperature in Row B.
>
> I have a macro which queries the user for a start date and time and
> then copies the temperature and time that fall in the querioed range
> into columns D and E. Precisely, it copies a formula in those rows
> which show the value if its in the queried range.
>
> I think a simple copy -> paste special would do away with the formulas
> and only leave the values.
>
> THEN, I need to chart this data. X axis has date/time. Y axis has
> temperature.
>
> The start and end point as well as amount of data points changes each
> time so the macro has to select that itself...
>
> Cant figure it out
>
> Matt
>
>

[Matt]

this causes an error:

SelectedCells = Union(SelectedCells, CheckCell.Range("A1:B1")