I am using the following forumula to workout some calculation. But after that
calculation I need to change its formula into Value in the same range given
the formula ie (from ActiveCell to lastrow.cell) . I need to keep its value
on the same column insted of formula. Please need ur help

Orginal forumal is

ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([Item.xls]Car!C2,RC7),VLOOKUP(RC7,[Item.xls]Car!C2:C12,11,0),IF(COUNTIF([Item.xls]Truck!C2, RC7),VLOOKUP(RC7,[Item.xls]truck!C2:C12,11,0),0))"

res = ActiveCell.Address

res1 = Mid(res, 2, 1)
With ActiveSheet
lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
.Range(res & ":" & res1 & lastrow).FormulaR1C1 =
..Range(res).FormulaR1C1
End With

If I understand you correctly, you want to replace the formula in a cell with
the value that it calculated?

anyCell.Formula = anyCell.Value

type of statement will do that. But I'm not certain that's what you're
looking for.


"pol" wrote:

I am using the following forumula to workout some calculation. But after that
calculation I need to change its formula into Value in the same range given
the formula ie (from ActiveCell to lastrow.cell) . I need to keep its value
on the same column insted of formula. Please need ur help

Orginal forumal is

ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([Item.xls]Car!C2,RC7),VLOOKUP(RC7,[Item.xls]Car!C2:C12,11,0),IF(COUNTIF([Item.xls]Truck!C2, RC7),VLOOKUP(RC7,[Item.xls]truck!C2:C12,11,0),0))"

res = ActiveCell.Address

res1 = Mid(res, 2, 1)
With ActiveSheet
lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
.Range(res & ":" & res1 & lastrow).FormulaR1C1 =
.Range(res).FormulaR1C1
End With