How do I convert this Excel formula into VBA code?

Question

'I am using this formula in an Excel worksheet, in cell A6. It is working fine.

=IF(O6="Hand","Manual Entry",IF(O6="JET",R6,IF(O6="COKE","Red Bull",IF(O6="Freight","Logistics",IF(O6="TAX","Tax",IF(O6="TRANSFER COST","Transfer Cost Transactions",IFERROR(IF(FIND("INV#",R6,1)>=1,MID(R6,FIND("INV#",R6,1),10),""),"")))))))

Now, my question is: how do I convert this to VBA? I have tried recording it, and the code is as follows:

ActiveCell.FormulaR1C1 = _ "=IF(RC[14]=""Hand"",""Manual Entry JE"",IF(RC[14]=""JET"",RC[17],IF(RC[14]=""COKE"",""Red Bull"",IF(RC[14]=""FREIGHT"",""Logistics"",IF(RC[14]=""TAX"",""Tax"",IF(RC[14]=""TRANSFER COST"",""Transfer Cost Transactions"",IFERROR(IF(FIND(""INV#"",RC[17],1)>=1,MID(RC[17],FIND(""INV#"",RC[17]" & _ """""),"""")))))))"

When I run this, I am receiving Run Time Error 1004: Application-defined or object-defined error.So I Changed this to something like this, this doing same as above formula except the find option, everything is running fine. ![VBA For Above formula][Any Help on the find?] End sub.How do i get the fine option in the above VBA code.`

Answer 1

This works for me:

Range("L6").Formula = "=IF(O6=""Hand"",""Manual Entry"",IF(O6=""JET"",R6,IF(O6=""COKE"",""Red Bull"",IF(O6=""Freight"",""Logistics"",IF(O6=""TAX"",""Tax"",IF(O6=""TRANSFER COST"",""Transfer Cost Transactions"",IFERROR(IF(FIND(""INV#"",R6,1)>=1,MID(R6,FIND(""INV#"",R6,1),10),""""),"""")))))))"

This is just your original Excel formula, but with the " characters escaped as "".

Answer 2

You need to escape your quotes.

ActiveCell.Formula = "=IF(O6=""Hand"",""Manual Entry"",IF(O6=""JET"",R6,IF(O6=""COKE"",""Red Bull"",IF(O6=""Freight"",""Logistics"",IF(O6=""TAX"",""Tax"",IF(O6=""TRANSFER COST"",""Transfer Cost Transactions"",IFERROR(IF(FIND(""INV#"",R6,1)>=1,MID(R6,FIND(""INV#"",R6,1),10),""""),"""")))))))"

And use .Formula since you're using specific cell names that are not relative to the currently selected cell.