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

Question

I am trying to fill in Values "A-Z, 0-9" in a 2007 Excel sheet in four different locations (I am trying to put "A-Z" and "0-9" in cells: A1 to D9, E1 to H9, A10 to D18, and E10 to 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 for loop where it does not use the Range function with the cells, I can only do this for cells A1:D9, and if I use the second for loop with the range, I get the value 9 appearing in every cell from A1 to D9.

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

Answer 1

An efficient method that avoids looping altogether is to use the fact that you can directly copy a matrix to a range (and vice-versa). A modification to Dick's code 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

Answer 2

Here's another way

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

Answer 3

Instead of populating an array, why not take advantage of the Chr function?

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

Answer 4

Ok first: You can do that in many different ways ... your main problem in your second loop was that you were always writing to a fixed range:

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

That instruction writes the current Character (determined by i and j) to the entire Range A1:D1 to A1:A10 over and over ... the last Character is "9" so the range is eventually being filled with "9".

Basically as you always want to write the same characters to different ranges, you don't have to copy the array over and over. Here is a piece of code that does what you want

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

My idea was (to keep it fairly simple) that you could use a 2D array holding the start coordinates of your tables (4 tables, 2 start coordinates each).

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

You can then iterate over that array and output the tables (always using the start row and column as reference). You can make the code even more flexible by using modulo and whatnot of course.

I hope that helps a bit.