How to add formula to Excel Cells using VBA

'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()"   'gives column number

'column 1
ws.range("a2:a10").formula = "=row()"  'gives row number

'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"