how to put formula in a cell
 

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

how to put formula in a cell
 

hey rob thanks a lot...

i had been struggling this for past so long. I really appreciate this group
for such good solutions.

Would there be any book recommended for VBA for purchasing which would deal
with most of such small things ? Or any good book for VBA reference

thanks a lot

Monika
"Rob van Gelder" wrote in message
...
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

how to put formula in a cell
 

I can't recommend any books as I don't own any and haven't read any.

One book I've heard a lot of positive comments about is:
Excel 2002 VBA: Programmers Reference
by Rob Bovey (Author), Stephen Bullen (Author), John Green (Author), Robert
Rosenberg (Author)


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


"monika" wrote in message
...
hey rob thanks a lot...

i had been struggling this for past so long. I really appreciate this
group
for such good solutions.

Would there be any book recommended for VBA for purchasing which would
deal
with most of such small things ? Or any good book for VBA reference

thanks a lot

Monika
"Rob van Gelder" wrote in message
...
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