[RESOLVED] Excel - Autofill Multiplication table type ranges.

[brucevde]

Is there a quick way to autofill the following sample sheet

    A           B        C        D       V
1               20       22       24 ...  60
2  100        =A2*B1   =A2*C1
3  125        =A3*B1   =A3*C1
4  150        =A4*B1
.
.
.
   10000

[westconn1]

you can play around with this code
range("b1:d1").AutoFill range("b1:v1")
where b1 to d1 has the initial data (20,22,24) already filled
same for column A


try like
range("b2:v" & lastrow).formula = "=$a2* b$1"
to fill the formulas for the entire range

if you want the complete code let me know, i wrote it already

[anhn]

Try this:

    With Sheet1
        .[A2] = 100
        .[A3:A398].FormulaR1C1 = "=R[-1]C+25"
        .[B1] = 20
        .[C1:V1].FormulaR1C1 = "=RC[-1]+2"
        .[B2:V398].FormulaR1C1 = "=RC1*R1C"
    End With

[westconn1]

a different code, same result

Range("b1") = 20
Range("c1") = 22
Range("d1") = 24
Range("b1:c1").AutoFill Range("b1:v1")
Range("a2") = 100
Range("a3") = 125
Range("a4") = 150
Range("a2:a4").AutoFill Range("a2:a38")
Range("B2:v38").Formula = "=$a2* b$1"

[anhn]

That is very good pete. But you have 2 extra lines (3. and 7.) that are not required.

    [B1] = 20
    [C1] = 22
    [B1:C1].AutoFill [B1:V1]
    [A2] = 100
    [A3] = 125
    [A2:A3].AutoFill [A2:A398]
    [B2:V398].Formula = "=$A2*B$1"

Another way that use an array and 3 ForNext loops. The code is longer but faster:

    Dim r As Long, c As Long
    Dim ar(1 To 398, 1 To 22)
    
    For c = 2 To 22
        ar(1, c) = 20 + 2 * (c - 2)
    Next
    For r = 2 To 398
        ar(r, 1) = 100 + 25 * (r - 2)
        For c = 2 To 22
            ar(r, c) = ar(r, 1) * ar(1, c)
        Next
    Next
    [A1].Resize(398, 22) = ar

[mipunflu]

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
______________
Calcul taux d'interet immobilier meilleur banque | Taux d interets legal emprunt actuel | Calcul taux d'interet immobilier

[westconn1]

That is very good pete. But you have 2 extra lines (3. and 7.) that are not required.

i always like 3 cells for autofill, in some cases 2 can be ambiguous, as you say probably not required here

i really like the resize for filling the range from array, much nicer than using offsets to the ubounds

[brucevde]

Thanks for the replies.

I actually searched for "multiplication table" in Excel help and was surprised to get a few hits. One topic was called "Create a Multiplication Table", go figure. It really pays to RTFM. Following is the information from the Help file.

A multiplication table is a two-variable data table.

Set up a worksheet with the following structure.   
   A
1  1 
2  1
3  =A1*A2 
 
Enter a row of values from B3 to the right. For example, 1 through 10. 
Enter a column of values from A4 down. For example, 1 through 10. 
Select all cells in the range except cells A1 and A2. 
On the Data menu, click Table. 
In the Row input cell box, enter A1. 
In the Column input cell box, enter A2.