Reference to a Cell or Range of Cells

One of the most important things you ll need to master is the technique of making a reference to a cell or range of cells. In brief, you ll need to be able to send values from a worksheet to a module sheet so that you can perform operations on the worksheet data, and you ll need to be able to send the results back from the module sheet to the worksheet. 

You can reference a particular cell or range within a worksheet in a number of ways by selecting a cell or range by using the Select keyword, by using the Selection or ActiveCell keywords, or by using the Range method or the Cells method. 

Range( D1 D20 ).Select Selection.Copy Sheets( Sheet15 ).Select Range( A1 ).Select Actives beet. Paste 

---

Instead of entering a number in a cell, you can enter an equation (called a formula in Microsoft Excel) that will calculate and display a result. Usually formulas refer to the contents of other cells by using cell references, such as A2, a reference to a cell, or B5 B12, a reference to a range of cells. The value displayed in a cell containing a formula will be automatically updated if values elsewhere in the worksheet are changed. Formulas can contain values, arithmetic operators and other operators, cell references, the wide range of Excel s worksheet functions, and parentheses. 

REF is displayed when a formula refers to a cell that has been deleted or is beyond the range of a reference used as an argument in a function. 

Instead of a macro that gets the contents of the active cell or a selection, you may want to create a macro that copies values from specified rows and columns in a worksheet, independent of where the cursor has been "parked" by the user. To accomplish this, you can use either the Range method or the Cells method to make a reference to a cell or range. The syntax of the Cells method is Cells(roiv, column). The first two of the following refer to cell B3, the next two to the range B3 E27. 

Generalized Two-Dimensional (2D) correlation analysis is a powerful tool applicable to data obtained from a very broad range of measurements, such as chromatography or infrared spectroscopy. Relationships among systematic variations in infrared spectra are obtained as a function of spectroscopic frequencies. In this paper, the variation is induced by the introduction of small doses of CO in the catalytic cell, inducing a pressure change and a modification of adsorbed CO concentration. The correlation intensities are displayed in the form of 2D maps, usually referred to as 2D correlation spectra. 2D correlation analysis can help us to solve the complexity of the spectra... 

The spreadsheet (Fig. 47.1) is divided into rows (identified by numbers) and columns (identified by alphabetic characters). Each individual combination of column and row forms a cell which can contain a data item, a formula, or a piece of text called a label. Formulae can include scientific and/or statistical functions and/or a reference to other cells or groups of cells (often called a range). Complex systems of data input and analysis can be constructed (models). The analysis, in part or complete, can be printed out. New data can be added at any time and the sheet recalculated. You can construct templates, pre-designed spreadsheets containing the formulae required for repeated data analyses. 

A name can be applied to a cell, a range of cells, a value or a formula. Most often you ll use names for cell references. Using names makes it easier to create and to decipher complex formulas. For example, the formula... 

To assign a name to a cell reference, first select the cell or range. Then choose Define... from the Name submenu to display the Define Name dialog box (Figure 3-2). Excel will propose a name in the Name box, using text from the cell immediately above or to the left of the selected cell. The absolute reference of the selected cell will appear in the Refers To box. Edit the name if desired, then press OK. 

The OFFSET reference, rows, columns, height, iv/dfh) function returns a reference offset from a given reference in a one- or two-dimensional range of cells. Thus, although INDEX and OFFSET are similar, INDEX returns only a single value from a one- or two-dimensional range of cells, while OFFSET can return a reference to a range of cells. 

The reference argument can be a reference to a single cell or a range. If reference is a range of cells and the optional arguments height and width are omitted, then OFFSET returns a reference of the same dimensions as reference. To select a single cell, the formula =OFFSET(reference, rows, columns, 1, 1) must be used. 

In Excel, the terms range, array and matrix are essentially identical. They all simply refer to a range of cells in a worksheet. They can either be one- or two-dimensional. 

Note the difference between the active cell and the selection Selection can be a range of cells or a single cell ActiveCell (always a single cell) refers to the cell in the upper-left comer of a range if applied to a range of cells. 

---