Excel formula in VBA Hello, I have just stumbled across an old piece of coding w

Question

Excel formula in VBA

Hello,

I have just stumbled across an old piece of coding written by a former colleague of mine. In the middle of the code, it tries to enter the Excel formula:

Range("BU2").Select

ActiveCell.FormulaR1C1 = "=IF(AND([RC-68]=""Employee only"",[RC-9]<>""Employee only""),""Downgrade"",IF(AND([RC-68]=""Employee & Partner"",[RC-9]=""Employee only""),""Upgrade"",IF(AND([RC-68]=""Employee & Partner"",[RC-9]=""Employee & Family""),""Downgrade"",IF(AND([RC-68]=""Employee & Partner"",[RC-9]=""Employee & Children""),""Upgrade/Downgrade"",IF(AND([RC-68]=""Employee & Family"",[RC-9]<>""Employee & Family""),""Upgrade"",IF(AND([RC-68]=""Employee & Children"",[RC-9]=""Employee only""),""Upgrade"",IF(AND([RC-68]=""Employee & Children"",[RC-9]=""Employee & Family""),""Downgrade"",IF(AND([RC-68]=""Employee & Children"",[RC-9]=""Employee & Partner""),""Upgrade/Downgrade"",""""))))))))"

Selection.AutoFill Destination:=Range("BU2:BU1000")

I was just intrigued by the fact that when I run the code, it stops at this point, yet when I type the formula straight into Excel it works fine. Any ideas???

Thanks for help !!

Explanation / Answer

Hi..

Well, for starters, the cell references like [RC-68] shhould be RC[-68] Your formula, if it working and is in R1C1 mode, will not have a bracket to the left of the R. Do a global replace in your code, reeplacing [RC with RC[ and your code will work.

When you want to enter a formula using VBA, the best thing is to get the formula working, then record a macro where you select the cell, enter edit mode, and re-enter the formula. Then the formula string in the recorded code will be correct.!!

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.