Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving an entire range and a for loop question
If I have a range of cells that is a row (eg. A1:E1), how do I increment it
by one row (eg. to make it A2:E2)? Furthermore, since VBA seems to lack the matrix access style of Java or C (ie. matrix[i][j] access), I think I need to use a for loop to access all of the cells in my range by using 'For Each cell In MyRange'. However, I have a static set of fields I want to fill the cells with (ie. A# needs to be from O2 on sheet4, B# needs to be from K5 on sheet4, etc), and I will be repeating this operation for several rows. How do I specifically select this? I was considering use a for loop with an integer parameter and then using a Select Case statement, but that seems rather inelegant. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving an entire range and a for loop question
If, by your "increment" (in your first question) you mean "move" as you
state in your Subject line, then try this... With Worksheets("Sheet3") .Range("A1:E1").Copy .Range("A2") .Range("A1:E1").Clear End With Just set the worksheet reference your specific worksheet (or possibly use ActiveSheet if that meets your needs better). Rick "Dreaded404" wrote in message ...[i] If I have a range of cells that is a row (eg. A1:E1), how do I increment it by one row (eg. to make it A2:E2)? Furthermore, since VBA seems to lack the matrix access style of Java or C (ie. matrix[j] access), I think I need to use a for loop to access all of the cells in my range by using 'For Each cell In MyRange'. However, I have a static set of fields I want to fill the cells with (ie. A# needs to be from O2 on sheet4, B# needs to be from K5 on sheet4, etc), and I will be repeating this operation for several rows. How do I specifically select this? I was considering use a for loop with an integer parameter and then using a Select Case statement, but that seems rather inelegant. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving an entire range and a for loop question
You can handle your second question like this...
Sub AssignValues() Dim X As Long Dim SourceSheet As String Dim DestinationSheet As String Dim Source() As String Dim Destination() As String SourceSheet = "Sheet4" DestinationSheet = "Sheet3" Source = Split("O2 K5") Destination = Split("A2 B2") For X = 0 To UBound(Source) Worksheets(DestinationSheet).Range(Destination(X)) = _ Worksheets(SourceSheet).Range(Source(X)) Next End Sub Set the SourceSheet and DestinationSheet variables to point to the appropriate worksheets, then fill in the **space** delimited strings (of cell addresses) being assigned to the Source and Destination arrays via the Split function calls (the space character is the default delimiter for the Split function, so it does not have to be specified... you can use any other character for your delimiter if you wish, a comma for example, just remember to include the optional second argument to the Split function calls if you do). Rick "Dreaded404" wrote in message ...[i] If I have a range of cells that is a row (eg. A1:E1), how do I increment it by one row (eg. to make it A2:E2)? Furthermore, since VBA seems to lack the matrix access style of Java or C (ie. matrix[j] access), I think I need to use a for loop to access all of the cells in my range by using 'For Each cell In MyRange'. However, I have a static set of fields I want to fill the cells with (ie. A# needs to be from O2 on sheet4, B# needs to be from K5 on sheet4, etc), and I will be repeating this operation for several rows. How do I specifically select this? I was considering use a for loop with an integer parameter and then using a Select Case statement, but that seems rather inelegant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop QUESTION to end of data range... | Excel Worksheet Functions | |||
Loop QUESTION to end of data range... | Excel Worksheet Functions | |||
Loop QUESTION to end of data range... | Excel Worksheet Functions | |||
Moving An Entire Row From One Sheet To Another | Excel Discussion (Misc queries) | |||
Moving entire rows | Excel Programming |