A Range cannot be created or populated the same way a string would:
Sub RangeTest()
Dim s As String
Dim r As Range 'Specific Type of Object, with members like Address, WrapText, AutoFill, etc.
' This is how we fill a String:
s = "Hello World!"
' But we cannot do this for a Range:
r = Range("A1") '//Run. Err.: 91 Object variable or With block variable not set//
' We have to use the Object approach, using keyword Set:
Set r = Range("A1")
End Sub
It is considered best practice to qualify your references, so from now on we will use the same approach here.
More about Creating Object Variables (e.g. Range) on MSDN . More about Set Statement on MSDN.
There are different ways to create the same Range:
Sub SetRangeVariable()
Dim ws As Worksheet
Dim r As Range
Set ws = ThisWorkbook.Worksheets(1) ' The first Worksheet in Workbook with this code in it
' These are all equivalent:
Set r = ws.Range("A2")
Set r = ws.Range("A" & 2)
Set r = ws.Cells(2, 1) ' The cell in row number 2, column number 1
Set r = ws.[A2] 'Shorthand notation of Range.
Set r = Range("NamedRangeInA2") 'If the cell A2 is named NamedRangeInA2. Note, that this is Sheet independent.
Set r = ws.Range("A1").Offset(1, 0) ' The cell that is 1 row and 0 columns away from A1
Set r = ws.Range("A1").Cells(2,1) ' Similar to Offset. You can "go outside" the original Range.
Set r = ws.Range("A1:A5").Cells(2) 'Second cell in bigger Range.
Set r = ws.Range("A1:A5").Item(2) 'Second cell in bigger Range.
Set r = ws.Range("A1:A5")(2) 'Second cell in bigger Range.
End Sub
Note in the example that Cells(2, 1) is equivalent to Range("A2"). This is because Cells returns a Range object.
Some sources: Chip Pearson-Cells Within Ranges; MSDN-Range Object; John Walkenback-Referring To Ranges In Your VBA Code.
Also note that in any instance where a number is used in the declaration of the range, and the number itself is outside of quotation marks, such as Range("A" & 2), you can swap that number for a variable that contains an integer/long. For example:
Sub RangeIteration()
Dim wb As Workbook, ws As Worksheet
Dim r As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
For i = 1 To 10
Set r = ws.Range("A" & i)
' When i = 1, the result will be Range("A1")
' When i = 2, the result will be Range("A2")
' etc.
' Proof:
Debug.Print r.Address
Next i
End Sub
If you are using double loops, Cells is better:
Sub RangeIteration2()
Dim wb As Workbook, ws As Worksheet
Dim r As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
For i = 1 To 10
For j = 1 To 10
Set r = ws.Cells(i, j)
' When i = 1 and j = 1, the result will be Range("A1")
' When i = 2 and j = 1, the result will be Range("A2")
' When i = 1 and j = 2, the result will be Range("B1")
' etc.
' Proof:
Debug.Print r.Address
Next j
Next i
End Sub