如何将此 Excel 公式转换为 VBA 代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17193620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I convert this Excel formula into VBA code?
提问by Ajit Kumar Jena
'I am using this formula in an Excel worksheet, in cell A6. It is working fine.
'我在 Excel 工作表的单元格 A6 中使用此公式。它工作正常。
=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),""),"")))))))
=IF(O6="手动","手动输入",IF(O6="JET",R6,IF(O6="COKE","Red Bull",IF(O6="货运","物流",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:
现在，我的问题是：如何将其转换为 VBA？我试过录制，代码如下：
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]" & _ """""),"""")))))))"
ActiveCell.FormulaR1C1 = _ "=IF(RC[14]=""Hand"",""手动输入 JE"",IF(RC[14]=""JET"",RC[17],IF(RC[ 14]=""可乐"",""红牛"",IF(RC[14]=""运费"",""物流"",IF(RC[14]=""税务"",""税"",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.`
当我运行这个时，我收到运行时错误 1004：应用程序定义或对象定义的错误。所以我把它改成这样的，除了查找选项外，这与上面的公式相同，一切都运行良好。![VBA For Above formula][Any Help on the find?] End sub.How do I get the fine option in the above VBA code.`
回答by Jean-Fran?ois Corbett
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 "".
这只是您原来的 Excel 公式，但"字符转义为"".
回答by Ripster
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 .Formulasince you're using specific cell names that are not relative to the currently selected cell.
并使用，.Formula因为您使用的是与当前所选单元格无关的特定单元格名称。
