Moving an entire range and a for loop question

[Dreaded404]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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