How To Use Range in VBA

Worksheet.range returns a Range object that represents a cell or a range of cells.

1 2 3 4 5

Sub Sample1() Range("A1").Value = 3.14159 Range("A2").Value = 3.14159 Range("A2").Offset(1, 0).Value = 3.14159 End Sub

1 2 3 4 5

Sub Sample2() Range("A1").Formula = "=10*RAND()" Range("A2").Formula = "=10*RAND()" Range("A2").Offset(1, 0).Formula = "=10*RAND()" End Sub

1 2 3 4 5 6 7 8 9 10

Sub Sample3() Dim Val As Integer Val = 0 For Each c In Worksheets("Sheet1").Range("A1:D10") If c.Value < 0.001 Then c.Value = Val Val = Val + 1 End If Next c End Sub

Now, we define range name with use Formulas > Name Manager
Give a name to A1:D10 with "MyRange"

1 2 3 4 5 6 7 8 9 10

Sub Sample4() Dim Val As Integer Val = 0 For Each c In Worksheets("Sheet1").Range("MyRange") If c.Value < 0.001 Then c.Value = Val Val = Val + 1 End If Next c End Sub

Range also can use with Range(Cell1, Cell2)
example A1 = Cell(1,1) , D10 = Cell(10,4)
which Cell(Row, col)

1 2 3 4 5 6 7 8 9 10

Sub Sample5() Dim Val As Integer Val = 0 For Each c In Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 4)) If c.Value < 0.001 Then c.Value = Val Val = Val + 2 End If Next c End Sub

Download example file