CODEWITHSUNDEEP
excelvbaexcel-formula
Matthias Pospiech
Matthias Pospiech

Reputation: 3488

vba speed up writing formular to cell matrix

I have a matrix that is written to the sheet

Destination.Resize(UBound(Matrix, 2), UBound(Matrix, 1)).value = Application.Transpose(Matrix)

In this cells I want to replace every value 0 with NaN (not a number)

Application.ScreenUpdating = False
For Each c In Destination.Cells
    If c.value = 0 Then
        c.FormulaLocal = "=NV()"
    End If
Next

this however takes very long.

Any possibility to make this faster?

Upvotes: 0

Views: 97

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Is your NV() a typo or a UDF? I used NA() in my example below as NV() does not exist in Excel 2007. Assuming your code somewhere sets Destination to the complete relevant range (not done in the code above where you are using Resize, try:

EDIT lookat:= parameter added.

Destination.Replace what:=0, replacement:="=NA()", lookat:=xlWhole

Since you are adding multiple formulas, and not constants, you should also make the change suggested by Dave with regard to Calculation Mode. And there are other things that could also be "turned off" if necessary -- e.g: DisplayEvents, EnableEvents, Intgeractive, StatusBar

Upvotes: 1

Related Questions