arrays - 使用VBA在Excel 2007中的不同单元格中填充不同的字符串值

我试图在四个不同位置的2007 Excel工作表中填写值“ AZ，0-9”（我试图在单元格中将“ AZ”和“ 0-9”放在单元格中：A1至D9，到E1，H9到A10和D18到E10）。到目前为止，我有以下代码：Sub TwoDArrays()Dim Matrix(9, 4) As VariantDim Matrix2(9, 4) As VariantDim Matrix3(9, 4) As VariantDim Matrix4(9, 4) As VariantMatrix(1, 1) = "A"Matrix(1, 2) = "B"Matrix(1, 3) = "C"Matrix(1, 4) = "D"Matrix(2, 1) = "E"Matrix(2, 2) = "F"Matrix(2, 3) = "G"Matrix(2, 4) = "H"Matrix(3, 1) = "I"Matrix(3, 2) = "J"Matrix(3, 3) = "K"Matrix(3, 4) = "L"Matrix(4, 1) = "M"Matrix(4, 2) = "N"Matrix(4, 3) = "O"Matrix(4, 4) = "P"Matrix(5, 1) = "Q"Matrix(5, 2) = "R"Matrix(5, 3) = "S"Matrix(5, 4) = "T"Matrix(6, 1) = "U"Matrix(6, 2) = "V"Matrix(6, 3) = "W"Matrix(6, 4) = "X"Matrix(7, 1) = "Y"Matrix(7, 2) = "Z"Matrix(7, 3) = "0"Matrix(7, 4) = "1"Matrix(8, 1) = "2"Matrix(8, 2) = "3"Matrix(8, 3) = "4"Matrix(8, 4) = "5"Matrix(9, 1) = "6"Matrix(9, 2) = "7"Matrix(9, 3) = "8"Matrix(9, 4) = "9"Matrix2(1, 1) = "A"Matrix2(1, 2) = "B"Matrix2(1, 3) = "C"Matrix2(1, 4) = "D"Matrix2(2, 1) = "E"Matrix2(2, 2) = "F"Matrix2(2, 3) = "G"Matrix2(2, 4) = "H"Matrix2(3, 1) = "I"Matrix2(3, 2) = "J"Matrix2(3, 3) = "K"Matrix2(3, 4) = "L"Matrix2(4, 1) = "M"Matrix2(4, 2) = "N"Matrix2(4, 3) = "O"Matrix2(4, 4) = "P"Matrix2(5, 1) = "Q"Matrix2(5, 2) = "R"Matrix2(5, 3) = "S"Matrix2(5, 4) = "T"Matrix2(6, 1) = "U"Matrix2(6, 2) = "V"Matrix2(6, 3) = "W"Matrix2(6, 4) = "X"Matrix2(7, 1) = "Y"Matrix2(7, 2) = "Z"Matrix2(7, 3) = "0"Matrix2(7, 4) = "1"Matrix2(8, 1) = "2"Matrix2(8, 2) = "3"Matrix2(8, 3) = "4"Matrix2(8, 4) = "5"Matrix2(9, 1) = "6"Matrix2(9, 2) = "7"Matrix2(9, 3) = "8"Matrix2(9, 4) = "9"Matrix3(1, 1) = "A"Matrix3(1, 2) = "B"Matrix3(1, 3) = "C"Matrix3(1, 4) = "D"Matrix3(2, 1) = "E"Matrix3(2, 2) = "F"Matrix3(2, 3) = "G"Matrix3(2, 4) = "H"Matrix3(3, 1) = "I"Matrix3(3, 2) = "J"Matrix3(3, 3) = "K"Matrix3(3, 4) = "L"Matrix3(4, 1) = "M"Matrix3(4, 2) = "N"Matrix3(4, 3) = "O"Matrix3(4, 4) = "P"Matrix3(5, 1) = "Q"Matrix3(5, 2) = "R"Matrix3(5, 3) = "S"Matrix3(5, 4) = "T"Matrix3(6, 1) = "U"Matrix3(6, 2) = "V"Matrix3(6, 3) = "W"Matrix3(6, 4) = "X"Matrix3(7, 1) = "Y"Matrix3(7, 2) = "Z"Matrix3(7, 3) = "0"Matrix3(7, 4) = "1"Matrix3(8, 1) = "2"Matrix3(8, 2) = "3"Matrix3(8, 3) = "4"Matrix3(8, 4) = "5"Matrix3(9, 1) = "6"Matrix3(9, 2) = "7"Matrix3(9, 3) = "8"Matrix3(9, 4) = "9"Matrix4(1, 1) = "A"Matrix4(1, 2) = "B"Matrix4(1, 3) = "C"Matrix4(1, 4) = "D"Matrix4(2, 1) = "E"Matrix4(2, 2) = "F"Matrix4(2, 3) = "G"Matrix4(2, 4) = "H"Matrix4(3, 1) = "I"Matrix4(3, 2) = "J"Matrix4(3, 3) = "K"Matrix4(3, 4) = "L"Matrix4(4, 1) = "M"Matrix4(4, 2) = "N"Matrix4(4, 3) = "O"Matrix4(4, 4) = "P"Matrix4(5, 1) = "Q"Matrix4(5, 2) = "R"Matrix4(5, 3) = "S"Matrix4(5, 4) = "T"Matrix4(6, 1) = "U"Matrix4(6, 2) = "V"Matrix4(6, 3) = "W"Matrix4(6, 4) = "X"Matrix4(7, 1) = "Y"Matrix4(7, 2) = "Z"Matrix4(7, 3) = "0"Matrix4(7, 4) = "1"Matrix4(8, 1) = "2"Matrix4(8, 2) = "3"Matrix4(8, 3) = "4"Matrix4(8, 4) = "5"Matrix4(9, 1) = "6"Matrix4(9, 2) = "7"Matrix4(9, 3) = "8"Matrix4(9, 4) = "9"For i = 1 To 9For j = 1 To 4Cells(i, j) = Matrix(i, j)Next jNext i'For i = 1 To 9 'For j = 1 To 4 ' Range("a1:d1", "a1:a10").Value = Matrix(i, j) 'Application.WorksheetFunction.Transpose (Matrix)'Next j'Next iEnd Sub但是，在顶部for循环中，它不对单元格使用Range函数，我只能对单元格H18执行此操作，如果我对范围使用第二个for循环，则每个值中都会出现值9单元格从A1:D9到A1。那么有没有一种方法可以使我在上面指定的其他单元格中获得值“ A-Z”和“ 0-9”？ (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 好的，首先：您可以用许多不同的方法来做……第二个循环中的主要问题是，您总是在固定的范围内写入：Range("a1:d1", "a1:a10").Value = Matrix(i, j)该指令一遍又一遍地将当前字符（由i和j确定）写入整个范围A1：D1至A1：A10 ...最后一个字符为“ 9”，因此该范围最终被“ 9”填充。基本上，由于您始终希望将相同的字符写入不同的范围，因此不必一遍又一遍地复制数组。这是一段您想要做的代码Sub TwoDArrays()Dim Matrix(9, 4) As VariantDim startCoords(4, 2) As IntegerMatrix(1, 1) = "A"Matrix(1, 2) = "B"Matrix(1, 3) = "C"Matrix(1, 4) = "D"Matrix(2, 1) = "E"Matrix(2, 2) = "F"Matrix(2, 3) = "G"Matrix(2, 4) = "H"Matrix(3, 1) = "I"Matrix(3, 2) = "J"Matrix(3, 3) = "K"Matrix(3, 4) = "L"Matrix(4, 1) = "M"Matrix(4, 2) = "N"Matrix(4, 3) = "O"Matrix(4, 4) = "P"Matrix(5, 1) = "Q"Matrix(5, 2) = "R"Matrix(5, 3) = "S"Matrix(5, 4) = "T"Matrix(6, 1) = "U"Matrix(6, 2) = "V"Matrix(6, 3) = "W"Matrix(6, 4) = "X"Matrix(7, 1) = "Y"Matrix(7, 2) = "Z"Matrix(7, 3) = "0"Matrix(7, 4) = "1"Matrix(8, 1) = "2"Matrix(8, 2) = "3"Matrix(8, 3) = "4"Matrix(8, 4) = "5"Matrix(9, 1) = "6"Matrix(9, 2) = "7"Matrix(9, 3) = "8"Matrix(9, 4) = "9"startCoords(1, 1) = 1startCoords(1, 2) = 1startCoords(2, 1) = 1startCoords(2, 2) = 5startCoords(3, 1) = 10startCoords(3, 2) = 1startCoords(4, 1) = 10startCoords(4, 2) = 5For tableNo = 1 To UBound(startCoords) For rowNo = 1 To 9 For colNo = 1 To 4 Cells(rowNo + startCoords(tableNo, 1) - 1, colNo + startCoords(tableNo, 2) - 1) = Matrix(rowNo, colNo) Next colNo Next rowNoNext tableNoEnd Sub我的想法是（为了简单起见）可以使用一个2D数组来保存表的起始坐标（4个表，每个2个起始坐标）。A1 = 1,1E1 = 1,5A10 = 10,1E10 = 10,5然后，您可以遍历该数组并输出表（始终使用起始行和列作为参考）。当然，您可以使用模和其他方式使代码更加灵活。我希望能有所帮助。 (adsbygoogle = window.adsbygoogle || []).push({});