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

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

These statements replace the 6 If-Then statements you posted....

If iKN > 0 And iKN < 7 Then
Set CellRangePMR = Worksheets("PM").Range(Chr(66 + iKN) & _
"6:" & Chr(66 + iKNX) & CStr(PMR + 5))
End If

If the only values for iKN are 1 through 6 (for example, if they are being
generated by a loop counter), then you don't need the If-Then or End If
statements.

Rick


"Gijs Breedveld" <(E-Mail Removed)> wrote in message
news:1A3E92FA-0B05-41DB-8672-(E-Mail Removed)...
> 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

My side comment about the iKN values being generated by a loop counter is
misleading. What I meant to say...

For any given value of iKN, the single Set statement will generate the
correct cell range to be assigned to CellRangePMR... the If-Then "housing"
is there just to make sure the value of iKN is in range. If your method of
assigning a value to iKN is such that you will always know it is in range,
then you don't have to check for that with the If-Then statement I
provided... just use the single-line Set statement and you are done.

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> These statements replace the 6 If-Then statements you posted....
>
> If iKN > 0 And iKN < 7 Then
> Set CellRangePMR = Worksheets("PM").Range(Chr(66 + iKN) & _
> "6:" & Chr(66 + iKNX) & CStr(PMR + 5))
> End If
>
> If the only values for iKN are 1 through 6 (for example, if they are being
> generated by a loop counter), then you don't need the If-Then or End If
> statements.
>
> Rick
>
>
> "Gijs Breedveld" <(E-Mail Removed)> wrote in
> message news:1A3E92FA-0B05-41DB-8672-(E-Mail Removed)...
>> 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
>