Correcting a Formula Macro in a Complex Spreadsheet

Bright01
Bright01 used Ask the Experts™
on

I presently have a rather complex Spreadsheet that was constructed out of a number of EE Professional macros.  I've now made some formula changes and have what I hope is a simple problem to resolve.  If you open the attached file, you will find the spreadsheet LTV.  The Function button allows for the formulas to be returned to the appropriate cells if a absolute value is overwritten into a cell.  So for example, if you choose 5 in cell D4 you will get a 5 year analysis with the cells auto populated with formulas.  If you override, say F5 and make it a number it will behave correctly. If you press the Formula button, that is where I get a compile error.

Help!  And Thanks,

B.
Client-LTV-Assessment-v6.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Your code is missing an "=" after .Formula on numerous lines.  Add the "=" where applicable and it should work fine.

Excel VBA
hi B,

I think this is an easy one:
Try changing the below code by adding an equal sign after ".formula".

Sub Formula_Click()
Range("E5").Formula "=IF(AND($B5<>0,$C5=0),D5,IF($B5=0,D5,D5+(D5*$C5)))"
Range("E6").Formula "=IF(AND($B6<>0,$C6=0),D6,IF($B6=0,D6,D6+(D6*$C6)))"
Range("E8").Formula "=IF(AND($B8<>0,$C8=0),D8,IF($B8=0,D8,D8+(D8*$C8)))"
'...

Open in new window


So that it is written as:
Sub Formula_Click()
Range("E5").Formula = "=IF(AND($B5<>0,$C5=0),D5,IF($B5=0,D5,D5+(D5*$C5)))"
Range("E6").Formula = "=IF(AND($B6<>0,$C6=0),D6,IF($B6=0,D6,D6+(D6*$C6)))"
Range("E8").Formula = "=IF(AND($B8<>0,$C8=0),D8,IF($B8=0,D8,D8+(D8*$C8)))"
'...

Open in new window


hth
Rob

Author

Commented:
Both of you "nailed" this problem.  Much thanks.... now I can proceed on this project!

Much thanks,

B.
hi B,

Thanks for the points - after posting I saw cmeola had beaten me to it, so I really wasn't expecting any points. I'm quite happy for cmeola to have all the points for this one, after all, he/she beat me fair & square by 6 minutes.

Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial