Cells Within Ranges
|
This page describes the Visual Basic procedures to access a particular cell in a range of cells, such as a named range. For example, if you have a named range "MyRange", you may want to refer to cell "C5" relative to this range. What follows is a reply written by Alan Beban to a newsgroup question. Alan has been generous enough to allow me to reproduce his answer here. What follows was posted by Alan Beban on March 17, 1998, to the microsoft.public.excel.programming newsgroup, and is � Copyright 1997, Alan Beban. Thanks, Alan.
|
||
|
================================================ I assume you will get one or more responses referring you to the Cells Method, and that is a reasonable place to start studying, particularly for an illustration of referring to cells with a single index number. This response, however, deals with the Item feature of VBA: documented as the Item Method in Excel5, and as the Item Property in Excel97. Range("A1:B10").Item(5,3) refers to Cell C5, i.e., the cell in the 5th row, third column, starting with A1 as the upper left corner, the 1,1 cell. I treat the following as the shorthand version of the Item feature, although the documentation is not entirely clear; but documentation aside, it has the functionality of the Item feature: Range("A1:B10")(5,3) refers to Cell C5. And if the name of A1:B10 is "MyRange", then Range("MyRange")(5,3) refers to Cell C5. It is not necessary for the cell to be within the range in order to be referenced this way. E.g., Range("A1:B10")(12,13) is a valid reference to Cell M12. Nor need the index numbers be positive; e.g., Range("D4:F6")(0,0) refers to Cell C3, and Range("D4:F6")(-1,-2) refers to Cell A2; recall that this based on the fact that Range("D4:F6")(1,1) refers to Cell D4. The Item feature is also recursive. E.g., Range("D4:F6")(2,2)(3,4) refers to Cell H7, i.e., the cell in the 3rd row, 4th column, starting with E5 as the upper left corner (since E5 is the cell in the 2nd row, 2nd column, starting with D4 in the upper left corner). Cells can also be referenced with a single index number. The counting goes left to right, then down one row and left to right again. E.g., Range("A1:B2")(1) refers to Cell A1 What is not documented is that this method of referencing continues down the worksheet; e.g., Range("A1:B2")(5) refers to Cell A3, Range("A1:B2")(14) refers to Cell B7, etc. Using single indexing with negative numbers is available, but it is idiosyncratic and not particularly useful. This single indexing method is useful for walking down a column; e.g., Range("D4")(1) refers to Cell D4, Range("D4")(2) refers to Cell D5, Range ("D4")(11) refers to Cell D14, etc. Similarly, a slight adaptation provides a useful syntax for walking across a row: Range("D4").Columns(2) refers to Cell E4, Range("D4").Columns(5) refers to Cell H4, etc. When coupled with Object variables, the Item feature provides fairly succinct and efficient code. E.g., after Set rng = Worksheets(1).[a1] the item feature allows any cell on the worksheet to be referenced with two index numbers as with the Cells Method; e.g., rng(3,4) refers to Cell D3. That's it for today, Alan Beban
|
|
|
|
|