Dim CellRangePMR As Range
Dim iKN As Long
Dim PMR As Long

'testing purposes
iKN = 1
PMR = 8

Set CellRangePMR = Nothing
Select Case iKN
Case Is = 1, 2, 3, 4, 5, 6
With Worksheets("PM")
Set CellRangePMR = .Range(.Cells(6, 2 + iKN), .Cells(PMR + 5, 2 + iKN))
'or
Set CellRangePMR = .Cells(6, 2 + iKN).Resize(PMR + 5 - 6 + 1, 1)
'or doing the arithmetic
Set CellRangePMR = .Cells(6, 2 + iKN).Resize(PMR, 1)
End With
End Select

If CellRangePMR Is Nothing Then
MsgBox "oh, oh"
Else
MsgBox CellRangePMR.Address(0, 0)
End If
Gijs Breedveld wrote:

How can I change the column charater in a loop for the following:

If iKN = 1 Then Set CellRangePMR = Worksheets("PM").Range("C6:c" & PMR + 5)
If iKN = 2 Then Set CellRangePMR = Worksheets("PM").Range("d6:d" & PMR + 5)
If iKN = 3 Then Set CellRangePMR = Worksheets("PM").Range("e6:e" & PMR + 5)
If iKN = 4 Then Set CellRangePMR = Worksheets("PM").Range("f6:f" & PMR + 5)
If iKN = 5 Then Set CellRangePMR = Worksheets("PM").Range("g6:g" & PMR + 5)
If iKN = 6 Then Set CellRangePMR = Worksheets("PM").Range("h6:h" & PMR + 5)

Best regards,

Gijs

--

Dave Peterson