ansaurus

Question

How to fill in Different String Values in Different Cells in Excel 2007 VBA marcos

Answer 1

+1 A:

Hi! 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.

das_weezul 2010-04-26 13:36:04

Answer 2

A:

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

2010-04-29 09:19:28

ansaurus

tags:

views:

answers:

How to fill in Different String Values in Different Cells in Excel 2007 VBA marcos

related questions