VBA输出二维数组表问题 [英] vba output 2D an array to sheet issue

问题描述

我想输出下面的二维数组在工作簿的工作表。在code运行，但数组是不可见的纸张上。请帮我找出错误。谢谢

 昏暗ř长，C长，vArr为VariantvArr =阵列（阵列（N，c.181C＆gt;一种，p.Q61K，，0.11），_
              阵列（C，c.98C＆gt;一种，p.S33Y，，36％），_
              阵列（K，c.2447A＆GT; T，p.D816V，，8），_
              阵列（B，c.1799T＆GT; T，p.V600E，，0.08），_
              阵列（N，c.181C＆gt;一种，p.Q61K，，0.11），_
              阵列（C，c.98C＆gt;一种，p.S33Y，，36），_
              阵列（N，c.181C＆gt;一种，p.Q61K，，0.11），_
              阵列（C，c.98C＆gt;一种，p.S33Y，，36））  对于R = LBOUND（vArr）为UBound函数（vArr）
    对于C = LBOUND（vArr（LBOUND（vArr）））为UBound函数（vArr（UBound函数（vArr）））
      'MSGBOXvArr（与＆amp; R放大器;）（与＆amp; C和;）=与＆amp; vArr（R） - （C）的
    下一个
  下一个
LRow =工作表（Sheet2的），范围（A＆放大器; Rows.count）。.END（xlUp）.Row表（Sheet2的）。选择
工作表（Sheet2的）的范围。（C和放大器; LRow + 1安培;：G＆放大器; LRow + 9）。价值= vArr
 

解决方案

有关一件事你试图粘贴一些东西，有8行到一个范围内具有9行。但是 - 更根本的问题是，在VBA数组的数组是从2个不同的 - 维数组。你应该转换为2-D阵列粘贴前：

 子测试（）    昏暗ř长，C长，vArr为Variant
    昏暗vArr2为Variant
    昏暗LRow只要    vArr =阵列（阵列（N，c.181C＆gt;一种，p.Q61K，，0.11），_
                  阵列（C，c.98C＆gt;一种，p.S33Y，，36％），_
                  阵列（K，c.2447A＆GT; T，p.D816V，，8），_
                  阵列（B，c.1799T＆GT; T，p.V600E，，0.08），_
                  阵列（N，c.181C＆gt;一种，p.Q61K，，0.11），_
                  阵列（C，c.98C＆gt;一种，p.S33Y，，36），_
                  阵列（N，c.181C＆gt;一种，p.Q61K，，0.11），_
                  阵列（C，c.98C＆gt;一种，p.S33Y，，36））    使用ReDim vArr2（0至7,0至4）
    当R = 0至7
        对于c = 0到4
            vArr2（R，C）= vArr（R） - （C）的
        下一个C
    接下来ř    LRow =工作表（Sheet2的），范围（A＆放大器; Rows.Count）。.END（xlUp）.Row    表（Sheet2的）。选择
    工作表（Sheet2的）的范围。（C和放大器; LRow + 1安培;：G＆放大器; LRow + 8）。价值= vArr2结束小组
 

I'm trying to output the following 2D array to a sheet in a Workbook. The code runs but the array is not visible on the sheet. Please help me find the error. Thanks

Dim R As Long, C As Long, vArr As Variant

vArr = Array(Array("N", "c.181C > a", "p.Q61K", "", "0.11"), _
              Array("C", "c.98C > a", "p.S33Y", "", "36%"), _
              Array("K", "c.2447A > T", "p.D816V", "", "8"), _
              Array("B", "c.1799T > T", "p.V600E", "", "0.08"), _
              Array("N", "c.181C > a", "p.Q61K", "", "0.11"), _
              Array("C", "c.98C > a", "p.S33Y", "", "36"), _
              Array("N", "c.181C > a", "p.Q61K", "", "0.11"), _
              Array("C", "c.98C > a", "p.S33Y", "", "36"))

  For R = LBound(vArr) To UBound(vArr)
    For C = LBound(vArr(LBound(vArr))) To UBound(vArr(UBound(vArr)))
      'MsgBox "vArr(" & R & ")(" & C & ") = " & vArr(R)(C)
    Next
  Next
LRow = Worksheets("Sheet2").Range("A" & Rows.count).End(xlUp).Row

Sheets("Sheet2").Select
Worksheets("Sheet2").Range("C" & LRow + 1 & ":G" & LRow + 9).Value = vArr

解决方案

For one thing you were trying to paste something which had 8 rows into a range with 9 rows. But -- the more fundamental problem is that in VBA an array of arrays is different from a 2 - dimensional array. You should convert to a 2-d array before pasting:

Sub test()

    Dim R As Long, C As Long, vArr As Variant
    Dim vArr2 As Variant
    Dim LRow As Long

    vArr = Array(Array("N", "c.181C > a", "p.Q61K", "", "0.11"), _
                  Array("C", "c.98C > a", "p.S33Y", "", "36%"), _
                  Array("K", "c.2447A > T", "p.D816V", "", "8"), _
                  Array("B", "c.1799T > T", "p.V600E", "", "0.08"), _
                  Array("N", "c.181C > a", "p.Q61K", "", "0.11"), _
                  Array("C", "c.98C > a", "p.S33Y", "", "36"), _
                  Array("N", "c.181C > a", "p.Q61K", "", "0.11"), _
                  Array("C", "c.98C > a", "p.S33Y", "", "36"))

    ReDim vArr2(0 To 7, 0 To 4)
    For R = 0 To 7
        For C = 0 To 4
            vArr2(R, C) = vArr(R)(C)
        Next C
    Next R

    LRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

    Sheets("Sheet2").Select
    Worksheets("Sheet2").Range("C" & LRow + 1 & ":G" & LRow + 8).Value = vArr2

End Sub

这篇关于VBA输出二维数组表问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！