'Add many formulas to an excel worksheet with one command
'This will be demonstrated by making a multiplication table
'Francis de la Cerna
'A formula such as
' ws.range("a1:a3").formula = "=b1"
' means that excel will put a formula "=b1" in a1 and copy that to a2 through a3
' it will NOT put "=b1" in all those cells
' The result will look like
| ' | A | B | C |
| 1 | =b1 | ||
| 2 | =b2 | ||
| 3 | =b3 |
'If you don't know what a $ means in an excel formula, go to Help and
'search for "absolute reference" or "cell reference" or "range reference."
'Look for results which explains the difference between absolute and relative 'references.
'This script, will use a mix of both types of references. If it's not clear what
'is happening, try writing a formula in one cell, and then copy and paste it
'to multiple cells. That is basically how the formula method in range behaves.
set xl = createobject("excel.application")
set wb = xl.workbooks.add
set ws = wb.worksheets(1)
xl.visible = true
'row 1
ws.range("b1:j1").formula = "=column()"
'column 1
ws.range("a2:a10").formula = "=row()"
'column 2
ws.range("b2:b10").formula = "=$a2*b$1"
'column 3
ws.range("c2:c10").formula = "=$a2*c$1"
'this is tedious
'let's just add all columns at once
'column 4-10
ws.range("d2:j10").formula = "=$a2*d$1"
[Via]