How to enter formulas, including double quotes, with the range.formula method?

user_15430191 0
Viewed 38

I am trying to enter formulas with the range.formula method.

I am assuming the error is because the formula has double quotes as I want empty values if the IF statements are true.

I end up with

runtime '13' type mismatch

After trying to supplement quotes with Chr(34), I run into

runtime '1004' - Application-defined or object defined error.

Sheets("Form").Range("formRange1").Formula = "=IF(ISBLANK(frmAssetID),"",IF(frmAssetID>MAX(tblAssets[Asset ID]),"",XLOOKUP(frmAssetID&" - "&MAXIFS(tblAssets[ID Count],tblAssets[Asset ID],frmAssetID),tblAssets[Lookup],tblAssets[Item Reference])))"

Formula works correctly when manually pasted into the range formula bar.

vba excel
1 Answers

You need to pad the doube quotes so " becomes "" within the formula.

Try this (UNTESTED)

Sheets("Form").Range("formRange1").Formula = "=IF(ISBLANK(frmAssetID),"""",IF(frmAssetID>MAX(tblAssets[Asset ID]),"""",XLOOKUP(frmAssetID&"" - ""&MAXIFS(tblAssets[ID Count],tblAssets[Asset ID],frmAssetID),tblAssets[Lookup],tblAssets[Item Reference])))"

user_11405790
Related