Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
column loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
column loop
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
column loop
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
column loop
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)" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Column | Excel Discussion (Misc queries) | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Loop through Column and add hyperlink | Excel Programming | |||
Loop Column letter A to Z ?? | Excel Programming | |||
HOW-TO? Loop through cells in a column | Excel Programming |