CODEWITHSUNDEEP
vbaexcel
mgri
mgri

Reputation: 267

Evaluating only values from a formula in VBA?

I am able to execute a specific formula and assign it to a cell:

my_cell = Int(Cells(row, my_col).Value2) '# "row" is a row, "my_col" is a column, "i" is a counter
Cells(row, i).FormulaR1C1 = "=a working formula that returns a date as a number"
Cells(row, i).NumberFormat = "dd/mm/yyyy" '# format the number as a date

but I actually want to only write the value returned by the formula and not show the whole formula's syntax when selecting the destination cell.

I found this method and it works:

my_cell = Int(Cells(row, my_col).Value2)
Cells(row, i).FormulaR1C1 = "=a working formula that returns a date as a number"
Cells(row, i).Copy
Cells(row, i).PasteSpecial xlPasteValues
Cells(row, i).NumberFormat = "dd/mm/yyyy"

but it seems really slow (I have a lot of data) and not really performing as a solution since it copy-and-paste the value instead of directly evaluating and assigning it.

Is there a faster and more straightforward way to accomplish this task?

Upvotes: 0

Views: 67

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

If you see the formula instead of only the result, maybe you have activated the option "Display Formula" on the Options Ribbon?

Display Formulas

To turn it off using VBA just type:

ActiveWindow.DisplayFormulas = False

Could it be?

Upvotes: 1

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

Most of worksheet function is available in VBA. The syntax is:

WorksheetFunction.SomeFunction(arg1, arg2, ...)

so, you could use following code:

With WorksheetFunction
    Cells(row, i)= 'Your formula, with function preceeded with dot .
End With

Some worksheet functions are not available this way, for instance LEFT, but usually there is equivalent VBA function, so you my type Left without dot.

Upvotes: 3

Related Questions