Referring to cells

I am writing a macro that with the use of some code creates charts of some
data avaliable on a spreadsheet. My problem is that the cells that are to be
used for the charts are different every time. I do not know how to refer to a
cell "variably". The problem is that I search the spreadsheet for certain
texts and when found I want them to be e.g. headlines in the chart.

Set rng = Worksheets("Indata").Cells.Find("Date", LookIn:=xlValues)

This cell e.g. how do you refer to it or cells in the same row/column etc.?
rng.Address gives you the adress. but howe.g. loop something with it? what i
want to know is is there any way to get the adress from a cell in a format
that you e.g. can add 1 to the rows or columns?

Dim rng as Range
Dim rng1 as Range
Set rng = Worksheets("Indata").Cells.Find("Date", LookIn:=xlValues)

-----------------------
With Worksheets("Indata")
set rng1 = .Range(rng,rng1.End(xldown))
End With
msgbox rng.Address, rng1.Address
----------------------


rng.offset(1,0)
is the next cell below the header

rng.offset(0,1)
is the next cell the right of the header

negative numbers are up and left

--
Regards,
Tom Ogilvy


"april27" wrote:

> I am writing a macro that with the use of some code creates charts of some
> data avaliable on a spreadsheet. My problem is that the cells that are to be
> used for the charts are different every time. I do not know how to refer to a
> cell "variably". The problem is that I search the spreadsheet for certain
> texts and when found I want them to be e.g. headlines in the chart.
>
> Set rng = Worksheets("Indata").Cells.Find("Date", LookIn:=xlValues)
>
> This cell e.g. how do you refer to it or cells in the same row/column etc.?
> rng.Address gives you the adress. but howe.g. loop something with it? what i
> want to know is is there any way to get the adress from a cell in a format
> that you e.g. can add 1 to the rows or columns?

with your cursor on the sheet desired>insert>name>define>name it
whateveryoulike>in the refers to box
=offset($A$1,0,0,counta($a:$a),6) 'look in help index for OFFSET
in the chart source type in
=yourworkbookname.xls!whateveryoulike
now the chart will be SELF adjusting.
--
Don Guillett
SalesAid Software
[email protected]
"april27" <[email protected]> wrote in message
news:[email protected]...
>I am writing a macro that with the use of some code creates charts of some
> data avaliable on a spreadsheet. My problem is that the cells that are to
> be
> used for the charts are different every time. I do not know how to refer
> to a
> cell "variably". The problem is that I search the spreadsheet for certain
> texts and when found I want them to be e.g. headlines in the chart.
>
> Set rng = Worksheets("Indata").Cells.Find("Date", LookIn:=xlValues)
>
> This cell e.g. how do you refer to it or cells in the same row/column
> etc.?
> rng.Address gives you the adress. but howe.g. loop something with it? what
> i
> want to know is is there any way to get the adress from a cell in a format
> that you e.g. can add 1 to the rows or columns?

Range.Offset(rowoffset, columnoffset).
I.e., Range("D6").Offset(-2,3) would be Range("G4"), 2 rows up, 3
columns to the right.
If the values you seek are in the 5 cells below your found cell, the
range to graph would be Range(rng.offset(1,0),rng.offset(6,0))


april27 wrote:
> I am writing a macro that with the use of some code creates charts of some
> data avaliable on a spreadsheet. My problem is that the cells that are to be
> used for the charts are different every time. I do not know how to refer to a
> cell "variably". The problem is that I search the spreadsheet for certain
> texts and when found I want them to be e.g. headlines in the chart.
>
> Set rng = Worksheets("Indata").Cells.Find("Date", LookIn:=xlValues)
>
> This cell e.g. how do you refer to it or cells in the same row/column etc.?
> rng.Address gives you the adress. but howe.g. loop something with it? what i
> want to know is is there any way to get the adress from a cell in a format
> that you e.g. can add 1 to the rows or columns?