- The Range Object
- 语法指定一个范围
- Named Ranges
- Shortcut for Referencing Ranges
- 参考范围in Other Sheets
- Referencing a Range Relative to Another Range
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Using the Union Method to Join Multiple Ranges
- Using the Intersect Method to Create a New Range from Overlapping Ranges
- Using the IsEmpty Function to Check Whether a Cell Is Empty
- Using the CurrentRegion Property to Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
Using theOffsetProperty to Refer to a Range
You have already seen a reference toOffsetwhen you recorded a relative reference.Offsetenables you to manipulate a cell based on the location of another cell, such as the active cell. Therefore, you do not need to know the address of the cell you want to manipulate.
The syntax for theOffsetproperty is as follows:
的范围内。抵消(RowOffset ColumnOffset)
For example, the following code affects cell F5 from cell A1:
Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)
Or, shorter yet, you can write this:
Range("A1").Offset(4,5)
The count of the rows and columns starts at A1 but does not include A1.
If you need to go over only a row or a column, but not both, you don’t have to enter both the row and the column parameters. To refer to a cell one column over, use one of these lines:
Range("A1").Offset(ColumnOffset:=1) Range("A1").Offset(,1)
Both of these lines mean the same, so the choice is yours. If you use the second line, make sure to include the comma so Excel knows that the1refers to theColumnOffsetargument. Referring to a cell one row up is similar:
Range("B2").Offset(RowOffset:=-1) Range("B2").Offset(-1)
Once again, you can choose which one to use. It is a matter of readability of the code.
Suppose you have a list of produce in column A, with totals next to the produce items in column B. If you want to find any total equal to zero and placeLOWin the cell next to it, do this:
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _ LookIn:=xlValues) Rng.Offset(, 1).Value = "LOW"
When used in aSuband looping through a data set, it would look like this:
Sub FindLow() With Range("B1:B16") Set Rng = .Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues) If Not Rng Is Nothing Then firstAddress = Rng.Address Do Rng.Offset(, 1).Value = "LOW" Set Rng = .FindNext(Rng) Loop While Not Rng Is Nothing And Rng.Address <> firstAddress End If End With End Sub
TheLOWtotals are noted by the program, as shown inFigure 3.1.
Figure 3.1Find the produce with zero totals.
抵消不仅仅是单个细胞;你可以我们e it with ranges. You can shift the focus of a range over in the same way you can shift the active cell. The following line refers to B2:D4 (seeFigure 3.2):
Range("A1:C3").Offset(1,1)
Figure 3.2Offsetting a range:Range("A1:C3").Offset(1,1).Select.