How to use formula in excel using vbscript

'Assigning formula to the cells

objExcel.Cells(6, 1).Formula = "=COUNTIF(A1:A4,'NK*')"  'this is how to use Countif function in the vbscript, here this will look for NK and count the number of cells that has NK.


i,e
strFormula = "=COUNTIF(A1:A4," & Chr(34) & "NK*" & Chr(34) & ")"
objExcel.Cells(6, 1).Formula = strFormula  'use .Formula, to assign formula to the cells 

Ref: http://blogs.technet.com/b/heyscriptingguy/archive/2006/07/13/how-can-i-add-a-countif-formula-to-an-excel-spreadsheet.aspx


'Assigning formula to the Range

Function1 = "=Countif(A1:A5,"hi")"    'here 'hi' is counted in each instance b/w A1 to A5

Sheets(1).Range("A1").FormulaLocal = Function1 'use .FormulaLocal, to assign formula to the Range of cells.



' Now make the formulas dynamic

One can use INDIRECT function along with main function.