Fill in various string values in different Excel 2007 cells using VBA

Question

Fill in various string values in different Excel 2007 cells using VBA

I am trying to fill in the values "AZ, 0-9" in an Excel Excel worksheet in four different places (I am trying to put in the cells "AZ" and "0-9": A1before D9, E1before H9, A10before D18and E10before H18).

So far I have this code:

Sub TwoDArrays()
Dim Matrix(9, 4) As Variant
Dim Matrix2(9, 4) As Variant
Dim Matrix3(9, 4) As Variant
Dim Matrix4(9, 4) As Variant

Matrix(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 9
For j = 1 To 4
Cells(i, j) = Matrix(i, j)
Next j
Next 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 i


End Sub

However, at the top of the loop, where it does not use the Range function with cells, I can only do this for cells A1:D9, and if I use the second for loop with a range, I get a value of 9 appearing in each cell from A1to D9.

So, is there a way to make it so that I can get the values "AZ" and "0-9" in the other cells mentioned above?

+3

arrays vba excel-vba multidimensional-array excel

user325160 24 . '10 23:13

4

das_weezul · Answer 1 · 2010-04-26T13:36:04+0000

: -. , :

Range("a1:d1", "a1:a10").Value = Matrix(i, j)

( j) A1: D1-A1: A10 ... "9", "9".

, , . , ,

Sub TwoDArrays()
Dim Matrix(9, 4) As Variant
Dim startCoords(4, 2) As Integer

Matrix(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) = 1
startCoords(1, 2) = 1

startCoords(2, 1) = 1
startCoords(2, 2) = 5

startCoords(3, 1) = 10
startCoords(3, 2) = 1

startCoords(4, 1) = 10
startCoords(4, 2) = 5


For 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 rowNo

Next tableNo

End Sub

, 2D-, (4 , 2 ).

A1 = 1,1 E1 = 1,5 10 = 10,1 E10 = 10,5

( ). , modulo , , .

, .

dendarii · Answer 2 · 2010-04-29T09:19:28+0000

, , Chr?

Sub PopulateAlphaNum()

    Const intCOLS As Integer = 4
    Const intROWS As Integer = 9

    Dim rngStart As Range
    Dim rngAllRanges As Range
    Dim i As Integer
    Dim j As Integer
    Dim iChr As Integer

    Set rngAllRanges = Range("A1, E1, A10, E10")

    For Each rngStart In rngAllRanges

        iChr = 65

        For i = 0 To intROWS - 1
            For j = 0 To intCOLS - 1
                If iChr = 91 Then iChr = 48
                rngStart.Offset(i, j).Value = Chr(iChr)
                iChr = iChr + 1
            Next j
        Next i

    Next rngStart

End Sub

Dick Kusleika · Answer 3 · 2012-07-26T15:28:58+0000

Sub TwoDArrays()

    Dim aMatrix(1 To 9, 1 To 4) As String
    Dim i As Long, j As Long
    Dim lCnt As Long

    lCnt = 64

    For i = 1 To 9
        For j = 1 To 4
            If lCnt = 91 Then lCnt = 48
            lCnt = lCnt + 1
            aMatrix(i, j) = Chr$(lCnt)
        Next j
    Next i

    With Sheet1.Range("A1")
        For i = 0 To 9 Step 9
            For j = 0 To 4 Step 4
                .Offset(i, j).Resize(9, 4).Value = aMatrix
            Next j
        Next i
    End With

End Sub

Zairja · Answer 4 · 2012-07-26T15:39:07+0000

An effective method to completely eliminate the loop is to use the fact that you can directly copy the matrix into a range (and vice versa). Dick code modification gives the following solution:

Sub NoLoop()

Dim aMatrix(1 To 9, 1 To 4) As String
Dim i As Long, j As Long
Dim lCnt As Long

lCnt = 64

For i = 1 To 9
    For j = 1 To 4
        If lCnt = 90 Then lCnt = 47
        lCnt = lCnt + 1
        aMatrix(i, j) = Chr$(lCnt)
    Next j
Next i

Range("A1:D9") = aMatrix

End Sub

Fill in various string values ​​in different Excel 2007 cells using VBA

More articles:

Fill in various string values in different Excel 2007 cells using VBA