excel - 在 Excel VBA 中对向量进行排序

Question

我对 Excel VBA 很陌生。我想编写一个函数，将当前向量(用户选择的范围)中的单元格偏移用户指定的数量。

单元格必须向上移出数组“n”，然后必须在剩余单元格向上移动后显示在同一数组的底部，以代替向上移动并移出数组的单元格。

任何建议将不胜感激，我编写的当前代码不起作用，而且我知道的太少，无法帮助自己。

非常感谢!

Function ShiftVector(rng As Range, n As Integer)
    'User selects a vector and inputs an integer.
    'The vector must be sorted upwards by the amount equal to the entered integer

    Dim i As Integer, rw As Integer, temp As Variant

    rw = rng.rows.Count

    ReDim b(1 To rw) As Variant
    ReDim temp(1 To n) As Variant

    b = rng
    For i = 1 To n
        temp = b(i)
        'move the data in cells i=1 to n to the temporary array
    Next i

    b(i) = rng.Offset(-n, 0)
    'move the cells in array b up by n

    For i = rw - n To nr
        b(i) = temp
        i = i + 1

        'I'm not sure if this is correct: I want to replace the top shifted cells
        'back into the bottom of array b
     Next i
     ShiftVector4 = b

     'The function must output the newly assembled array b where
     'the top cells that were moved up n-spaces are now wrapped
     'around and are shown at the bottom of the array b
 End Function

Answer 1

像这样的东西应该工作:

Sub Tester()
    ShiftUp Range("B4:C13"), 3
End Sub


Sub ShiftUp(rng As Range, numRows As Long)
    Dim tmp
    With rng
        tmp = .Rows(1).Resize(numRows).Value
        .Rows(1).Resize(.Rows.Count - numRows).Value = _
          .Rows(numRows + 1).Resize(.Rows.Count - numRows).Value
        .Rows((.Rows.Count - numRows) + 1).Resize(numRows).Value = tmp
    End With
End Sub

作为 UDF:

Function ShiftUp(rng As Range, numRows As Long)
    Dim d, dOut, r As Long, c As Long, rMod As Long, rTot As Long
    Dim break As Long
    d = rng.Value
    dOut = rng.Value 'as a shortcut to creating an empty array....
    rTot = UBound(d, 1)
    break = rTot - numRows
    For r = 1 To rTot
        For c = 1 To UBound(d, 2)
            'figure out which input row to use...
            rMod = IIf(r <= break, r + numRows, -(break - r))
            dOut(r, c) = d(rMod, c)
        Next c
    Next r
    ShiftUp = dOut
End Function

请注意，这是一个数组公式，因此您需要选择一个与输入范围相同大小的范围，然后使用 CtrlShiftEnter 输入公式