CODEWITHSUNDEEP
excelvba
Matthew Struttmann
Matthew Struttmann

Reputation: 5

Create IF formula in a cell, with VBA, where result can be blank

I am trying to fill the cell to say =if(d5>0,D5*1.05,"") but I can not figure out how to have the false part of the statement fill in blank.

blank = ""
For i = 5 To 44
    Range("G" & i).Formula = "=V" & i
    Range("E" & i).Formula = "=IF(D" & i & ">0,D" & i & "*1.05," & blank & " )"
Next

Upvotes: 0

Views: 53

Answers (2)

Excelosaurus
Excelosaurus

Reputation: 2849

As mentioned by other posters, doubling a double quote when building a string will result in a single double quote character. Since you need 2 consecutive double quotes in the "else" part of your IF, type 4 of them.

What I'd like to now point out is that you don't need to loop over rows to accomplish what you're doing. You can set the Formula property for a range, building the formula as it should appear in the range's top left cell, and it will propagate automagically:

Option Explicit

Public Sub DoTheFormulaThing()
    Range("G5:G44").Formula = "=V5"
    Range("E5:E44").Formula = "=IF(D5>0,D5*1.05,"""")"
End Sub

When necessary, just put $ in the right places for absolute references.

Upvotes: 1

TechnoDabbler
TechnoDabbler

Reputation: 1265

Or use double quotes directly …

For i = 5 To 44
    Range("G" & i).Formula = "=V" & i
    Range("E" & i).Formula = "=IF(D" & i & ">0,D" & i & "*1.05,"""")"
Next

Upvotes: 2

Related Questions