在Excel VBA中对向量进行排序 [英] Sorting a vector in Excel VBA

问题描述

我是Excel VBA的新手.我想编写一个函数，使当前向量(用户选择的范围)中的像元偏移用户指定的数量.

I'm VERY new to Excel VBA. I want to write a function that offsets the cells in the current vector (the range selected by the user) by an amount also specified by the user.

必须将单元格向上移动"n"，然后必须在其余单元格向上移动以代替向上移动和移出单元格的位置之后，将其显示在同一阵列的底部.数组.

The cells must be moved up out of the array by "n", and must then be displayed at the bottom of the same array after the remaining cells have moved up to take the place of the cells shifted up and out of the array.

任何建议都将不胜感激，我编写的当前代码无法正常工作，我所知甚少.

Any advice will be greatly appreciated, the current code I wrote is not working and I know too little to help myself.

非常感谢！

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

推荐答案

类似的方法应该起作用:

Something like this should work:

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

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

Note this is an array formula, so you will need to select a range the same size as the input range and enter the formula using CtrlShiftEnter

这篇关于在Excel VBA中对向量进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！