Note that a the .Formula property accepts a string. When you write
"=if(val20 < 0,val1... then it's treating val1 as part of the string. The
formula will be looking for named ranges or labels called "val1" and "val20"

Try something like:
ActiveCell.Formula = "=if(" & val20 & " < 0," & val1 & "/(" & val20 &
"/100)," & val1 & "/0.94)"

But more likely you want to reference the address of the cell and not
hardcode the value into the formula:
ActiveCell.Formula = "=if(" & Cells(x, TYCol).Address & " < 0," & Cells(x,
CJCol).Address & "/(" & Cells(x, TYCol).Address & "/100)," & Cells(x,
CJCol).Address & "/0.94)"

Play with the parameters of .Address() to get the absolute/relative options
you desire.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"monika" wrote in message
...
hello...

I was initially using a recorded macro for putting a formula to range of
cells. now i want it to be converted to a to a proper logic. I am unable
to
put a formula to a cell. Which method or what do i need to use to put
it....Please see my code below:
I tried using Cells(x, VCol).Value...which didn't work. I activated the
particular cell and then used ActiveCell.Formula which also didn't work.
Would really appreciate if anyone could suggest something ...

thanks a lot.

Monika

Sub Macro1()
Dim lastcellnum As Integer
lastcellnum = Cells(Rows.Count, "A").End(xlUp).Row

Dim range1, range2, range As range
Dim VCol, TYCol, CJCol As Integer

Set range1 = Cells.Find("Volume") 'FINDING COLUMN Volume
VCol = range1.Column
Set range2 = Cells.Find("Test_yield") 'FINDING COLUMN Volume
TYCol = range2.Column
Set range3 = Cells.Find("Cum_Jan") 'FINDING COLUMN Volume
CJCol = range3.Column

Dim x As Integer
Dim val20 As Long
Dim val1 As Long

x = 2
With Workbooks("rough.xls").Worksheets("raw_data").Acti vate
While x <= lastcellnum
val20 = Cells(x, TYCol).Value
val1 = Cells(x, CJCol).Value
Cells(x, VCol).Activate
'Cells(x, VCol).Value = "=if(val20 < 0,val1/(val20/100),val1/0.94)"
ActiveCell.Formula = "=if(val20 < 0,val1/(val20/100),val1/0.94)"

x = x + 1
Wend
End With
End Sub