根据第2行值重新组织列

I am trying to sort columns alphabetically based on the values of cells in Row 2.

我试图根据第2行中单元格的值按字母顺序排序。

Can't figure out what's wrong here - it seems to work only for the first column and then it stops.

我不知道这里出了什么问题——它似乎只适用于第一列，然后就停止了。

Sub reorganise()
Dim v As Variant, x As Variant, findfield As Variant
Dim oCell As Range
Dim iNum As Long
Dim wsa As Worksheet

Set wsa = Worksheets("Skills")

v = Array(wsa.Range("B2", wsa.Cells(2, wsa.Columns.Count).End(xlToLeft)))

For x = LBound(v) To UBound(v)
findfield = v(x)
iNum = iNum + 1
Set oCell = wsa.Rows(2).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not oCell.Column = iNum Then
Columns(oCell.Column).Cut
Columns(iNum).Insert Shift:=xlToRight
End If
Next x
End Sub

2 个解决方案

#1

Ok I have figured it out.. was a bit more complicated but here is the full code:

好吧，我已经算出来了。有点复杂，但这里有完整的代码:

Sub reorganise()
Dim v
Dim x
Dim findfield As Variant
Dim oCell As Range
Dim iNum As Long
Dim wsa As Worksheet
Dim inputArray() As Variant

Set wsa = Worksheets("Skills")

With wsa
Set v = .Range("A2", .Cells(2, .Columns.Count).End(xlToLeft))
End With

v = Application.Transpose(v)

Call BubbleSort(v)

For x = LBound(v, 1) To UBound(v, 1)
findfield = v(x, 1)
iNum = iNum + 1
Set oCell = wsa.Rows(2).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not oCell.Column = iNum Then
Columns(oCell.Column).Cut
Columns(iNum).Insert Shift:=xlToRight
End If
Next x
End Sub

Sub BubbleSort(arr)
  Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long
  lngMin = LBound(arr, 1)
  lngMax = UBound(arr, 1)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If arr(i, 1) > arr(j, 1) Then
        strTemp = arr(i, 1)
        arr(i, 1) = arr(j, 1)
        arr(j, 1) = strTemp
      End If
    Next j
  Next i
End Sub

Basically in addition to what you said, I had to:

除了你所说的，我必须:

Transpose the array
转置数组
Change the LBound and Ubound and findfield syntax
更改LBound和Ubound以及findfield语法
Come up with an additional procedure to sort out the values of the array alphabetically
提出一个附加的过程来按字母顺序排序数组的值

#2

1) assign directly the range to your variant variable v - without the Array function. When using the array function, what you are doing is assign an array of one element - consisting of your range returned as an array - to your variable v

1)直接分配到变量v的范围——没有数组函数。当使用数组函数时，您要做的是向变量v分配一个元素数组(由数组返回的范围组成)

2) v will contain a 2 dimensions array:

2) v将包含一个二维数组:

first dimension will be 1 - for one row returned
第一个维度是1—对于返回的一行
second dimension will be as many columns as returned by the range
第二个维度将是由range返回的许多列。

Then loop through the second dimension of this array - I haven't checked the rest of the code but this should get you on your way

然后循环遍历这个数组的第二个维度——我还没有检查其余的代码，但是这应该会让您走上正轨

#1