通过Application.Index函数找到解决方案

Found solution via the Application.Index function

我只是通过试用Application.Index函数的一些不寻常变体找到了解决方案.

I found a solution simply by trying out some unusual variations of the Application.Index function.

Application.Index函数的某些特点

Some peculiarities of the the Application.Index function

通常，索引函数会通过行和列的位置来传递定义明确的项目，但其中一些并没有广为人知:

Typically the index function would deliver a well defined item by its row and column position, but there are some not so widely known pecularities:

1. 类似于Worksheet.Index函数，如果将行或列号参数设置为零(0)，则可以获取整个列或行项目.

1. Similarly to the Worksheet.Index function you can get the entire column or row items if the row or column number argument is set to zero (0).

可能使用数组参数-此功能不仅允许给定数字的已知索引指示，还允许数组参数提取行"或列"，因此可以通过Array(1,2,3)指示所需的一组列，例如A:C通过Array(1,2,3)作为列数组参数.

Use of array arguments possible - This function allows not only the known index indications by given numbers, but also array parameters to extract "rows" or "columns", so it's possible to indicate a wanted set of columns, e.g.A:C via Array(1,2,3) as column array argument.

过滤效果-此外，我了解到可以将选择减少到仅某些列(行)，例如通过Array(1,3) and even to change the internal order, e.g. Array(3,2,1)`.

Filtering effects - Furthermore I learnt that it is possible to reduce the choice to some columns (rows) only, e.g. via Array(1,3) and even to change the internal order, e.g.Array(3,2,1)`.

重组-然而，最令人惊讶的事实是，可以重复列选择，例如通过Array(1,1,2,3) 甚至Array(0,1,2,3)，其中0项与第1列相同.这可以用来达到与插入列相同的效果.

Restructuring - The most surprising fact, however, is that it is possible to repeat a column choice, e.g. via Array(1,1,2,3) or even Array(0,1,2,3) where the 0 item is the same as column 1. This can be used to reach the same effect as a column insertion.

提到的Index函数的最后的重组功能是我的方法的关键部分:

This last restructuring capability of the mentioned Index function is the key part of my approach:

代码示例

Sub AddFirstIndexColumn()
  Dim v, i&, ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("SourceSheet")  ' << change to source sheet name
' [1] get data
      v = ws.[A1:C5].Value2
' [2] define column array inserting first column (0 or 1) and preserving old values (1,2,3)
      v = Application.Index(v, _
          Application.Evaluate("row(1:" & UBound(v) & ")"), _
          Array(0, 1, 2, 3))   ' columns array where 0 reinserts the first column
' [3] add an current number in the first column
      For i = LBound(v) To UBound(v): v(i, 1) = i: Next i
 End Sub

如何测试结果

只需在上面的代码中插入以下内容:

Just insert the following to the code above:

' [4a] test result by debugging in immediate window
      For i = LBound(v) To UBound(v)
          Debug.Print "#" & i & ": " & Join(Application.Index(v, i, 0), ", ")
      Next i
' [4b] test result by writing back to target sheet
      Dim ws2 As Worksheet
      Set ws2 = ThisWorkbook.Worksheets("TargetSheet")  ' << change to target sheet name
      ws2.Range("A1").Resize(UBound(v), UBound(v, 2)).Offset(0, 0) = v

注意事项

找到的解决方案似乎仅限于65536行(可能类似于数组转置限制)，因此您不能将其用于更大的数据.

The found solution seems to be restricted to 65536 rows (possibly similar to array transposing limitation), so that you can't use it for bigger data.

最近的一些Application.Index示例

Some recent Application.Index examples

• 从sheet1复制到A，B，C，D，E，F，G，H列中的sheet2的A，B，C，G，F，R，S，T列
• 使用VBA进行多条件选择
• 如何加入返回以逗号分隔的命名范围中的值

• Copy from sheet1 columns A,B,C,G,F,R,S,T to sheet2 in columns A,B,C,D,E,F,G,H
• Multi criteria selection with VBA
• How to join returned values from named range separated by comma