How to put formula in VBA Code
Category: [Excel] Demo Available
How to put formula in VBA Code
Requirement:
The user is trying to automate a process, and a formula that worked fine when creating the template but won't run when the user tries to use VBA. The user currently gets a syntax error.
Here is the macro:
Range("L3").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-5]=R[-1]C,R[-1]C[5]<>""""),R[-1]C[5],IF(AND(RC[-5]=R[-1]C,R[-1]C[5]="""",R[-1]C[6]<>""""),R[-1]C[6],IF(AND(RC[-5]=R[-1]C,R[-1]C[5]="""",R[-1]C[6]="""",R[-1]C[7]<>""""),R[-1]C[7],IF(AND(RC[-5]=R[-1]C,R[-1]C[5]="""",R[-1]C[6]="""",R[-1]C[7]="""",R[-1]C[8]<>""""),R[-1]C[8],IF(AND(RC[-5]=R[-1]C,R[-1]C[5]="""",R[-1]C[6]="""",R[-1]C[7]="""",R[-1]C[8]="""",R[-1"& _
""""),R[-1]C[9],R[-1]C)))))"
Solution:
Code:
Range("L3").FormulaR1C1 = "=IF(AND(RC[-5]=R[-1]C,R[-1]C[5]<>""""),R[-1]C[5],IF(AND(RC[-5]" _
& "=R[-1]C,R[-1]C[5]="""",R[-1]C[6]<>""""),R[-1]C[6],IF(AND(RC[-5]=R[-1]C,R[-1]C[5]=""""," _
& "R[-1]C[6]="""",R[-1]C[7]<>""""),R[-1]C[7],IF(AND(RC[-5]=R[-1]C,R[-1]C[5]=""""," _
& "R[-1]C[6]="""",R[-1]C[7]="""",R[-1]C[8]<>""""),R[-1]C[8],IF(AND(RC[-5]=R" _
& "[-1]C,R[-1]C[5]="""",R[-1]C[6]="""",R[-1]C[7]="""",R[-1]C[8]="""",R[-1]C[9]<>""""),R[-1]C[9],R[-1]C)))))"
Obtained from the OzGrid Help Forum.
Solution provided by skywriter.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to install your new Excel VBA code |
| How to use a VBA code to change cells colours based on date in other cells |
| How to use VBA code to check interactions in the formula bar |
| How to use a VBA code for clipart |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.