Macro - Issue with IF statement being pasted into Cells

[grantboshart]

Hello all,

I am currently having an issue with the below formula being pasted into a cell.formula as shown. If I take away the If(C12<>"", , ) then the formula pastes just fine. when I paste the formula directly into a cell, it also works perfectly. Is there a way to have the macro not worry about the actual formula and just paste is word for word? Script below
vVv


Sub DefaultSlopes()

If MsgBox("Are you sure you want to change all pipe slopes back to default?" & vbNewLine & "This action cannot be reversed.", vbYesNo + vbExclamation, "Just making sure that wasn't an accident.") = vbYes Then

Range("R12:R100").Formula(I have also tried .Value) = "=IF(C12<>"",IF(P12=24, 0.18, IF(P12=30,0.13,IF(P12=36, 0.11, 0.1))),"")"

Else
End If
End Sub


Thanks!

 

[mole999]

have you tried recording that paste action

 

[grantboshart]

I would try that but I am not aware of a way to record a msgbox. I want the message box as a safety net for people who may accidentally hit the button.

 

[Biz]

I remember "" must have """". Does it help?

 

[grantboshart]

care to elaborate Biz?

 

[mole999]

you only need to record the formula being placed, the rest can be cobbled together

 

[Johnny C]

You nee to use a pair of double quotes for each double quote you want in the formula, otherwise VBA thinks you're ending the string.

Range("R12:R100").Formula(I have also tried .Value) = "=IF(C12<>"""",IF(P12=24, 0.18, IF(P12=30,0.13,IF(P12=36, 0.11, 0.1))),"""")"

 

[grantboshart]

You nee to use a pair of double quotes for each double quote you want in the formula, otherwise VBA thinks you're ending the string.

Range("R12:R100").Formula(I have also tried .Value) = "=IF(C12<>"""",IF(P12=24, 0.18, IF(P12=30,0.13,IF(P12=36, 0.11, 0.1))),"""")"

that is just what I needed to hear. Thank you!