Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to cells
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to cells
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to cells on a different worksheet | Excel Discussion (Misc queries) | |||
IF Formula referring to 2 cells | Excel Discussion (Misc queries) | |||
Referring to Cells | Excel Discussion (Misc queries) | |||
Sort a list with cells referring to others cells | Excel Discussion (Misc queries) | |||
referring to other cells with formulas | Excel Worksheet Functions |