I am kinda rookie in this area and would like to polish my xlsx file, but didn't find any easy of doing it. Let's say I have this table:

A | B

What I would like the result to be is something like this:

for i from 0 to 4 (i )

column A = B[i-1]

so the result looks like

CodePudding user response：

Instead of looping through all rows you might

• assign the source values to an array,
• rearrange them via Application.Index adding a new first element and
• write results back to the column residing left to the source range.

Sub ExampleCall()
'a) define source range
    Dim rng As Range
    Set rng = Sheet1.Range("B1:B5")   ' << change to project's sheet Code(Name)
'b) assign rearranged values to variant array by adding a 1st element 0
    Dim newOrder As Variant
    newOrder = Evaluate("Row(1:6)-1")   ' ~~> {0;1;2;3;4;5}
    Dim v As Variant
    v = Application.Index(rng.Value, newOrder, 1)
    v(1, 1) = 0          ' enter value 0 to first (=new) element
'c) write to left neighbour column
    rng.Offset(0, -1).Value = v
End Sub

Another way would be to just copy B1:B4 to A2:A5 and change A1 to value 0.

CodePudding user response：

Could be a simple loop as in:

For i = 1 To 5
    Cells(i,1) = Cells(i-1,2)
Next i

CodePudding user response：

Something like the below maybe?

For i = 1 To 5
    If i = 1 Then
        ActiveSheet.Range("A" & i).Value = 0
    Else
            ActiveSheet.Range("A" & i).Value = ActiveSheet.Range("B" & i - 1).Value
    End If
Next i