To give paste special (value) in macros

P

pol

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
 
J

JLatham

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help to to derive a formula 1
To store the formula in a string 4
Populate formula results in cells to next unique result. VBA or Fu 1
macro to copy following cells in the same column 4
Use array for lookup value, to return array of lookups 3
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Formula using array to find embedded value 9
Paste Special 4

Top