CODEWITHSUNDEEP
vbaexcel
Chris
Chris

Reputation: 357

Excel VBA formula as text issue

In my Excel 2016 VBA I have this code:

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(IF(E2=""Sgl"",K2,L2),"")"

when it gets placed in the cell it is the formula not the result. To manually fix this I have to change from Text to General and also the actual code needs changing:

Cell M2 says: =IFERROR(IF(E2="Sgl",K2,L2),") It should say: =IFERROR(IF(E2="Sgl",K2,L2),"") with the extra " at the end before the closing parenthesis

This was recorded (I removed the RC to the absolute cell reference initially when it wouldn't work), so I'm not sure what caused this or how to resolve it. Any help would be appreciated. Thanks

Upvotes: 1

Views: 6366

Answers (3)

xidgel
xidgel

Reputation: 3145

To generate the two double quotes ("") in IFERROR, you need to put 4 double quotes ("""") in the vba string literal.

Upvotes: 1

Chris
Chris

Reputation: 357

I fixed this by removing the IFERROR part as if there was no value in the proceeding columns it simply left it blank anyway rather than showing an error message:

Columns("M:P").Select
Selection.NumberFormat = "General"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]=""SGL"",RC[-2],RC[-1])"
Range("M2").Select

Upvotes: 0

Hambone
Hambone

Reputation: 16377

I think you just need to add the extra quote at the end. Because the quote denotes a string, you need to make two of them to yield one -- which means you need four to yield two.

Range("M2").Formula = "=IFERROR(IF(E2=""Sgl"",K2,L2),"""")"

Other notes of interest:

  • You don't need to Range().Select and then work against ActiveCell. Just invoke the methods/properties directly on the Range Object
  • I think in your case Formula will work. R1C1 is handy when the formulas are relative, but in this case, you are referencing actual cells. There is nothing wrong with what you did, but FYI

Upvotes: 3

Related Questions