Forum Discussion
Having VBA Macros upload a formula to a Cell
It's difficult to tell exactly what you're trying to do without an example of how your worksheet is set up. Would it be possible for you to upload a non-sensitive mock up file with fake data in it, so that the community can better understand your scenario?
After skimming your code I noticed that this formula is incorrect:
Hours = "=IF(Last!$B$6>0,(Last!$B$6-Last!$B$5)-SUM(Last!$B$7:$B$10),(IF(Last!$B$5="","",$C$1-Last!$B$5-SUM(Last!$B$7:$B$10))))"
In VBA if you use double quotes like in a worksheet formula you actually need to repeat them 4 times. So your formula should look like this:
Hours = "=IF(Last!$B$6>0,(Last!$B$6-Last!$B$5)-SUM(Last!$B$7:$B$10),(IF(Last!$B$5="""","""",$C$1-Last!$B$5-SUM(Last!$B$7:$B$10))))"
It's difficult to tell exactly what you're trying to do without an example of how your worksheet is set up. Would it be possible for you to upload a non-sensitive mock up file with fake data in it, so that the community can better understand your scenario?
After skimming your code I noticed that this formula is incorrect:
Hours = "=IF(Last!$B$6>0,(Last!$B$6-Last!$B$5)-SUM(Last!$B$7:$B$10),(IF(Last!$B$5="","",$C$1-Last!$B$5-SUM(Last!$B$7:$B$10))))"
In VBA if you use double quotes like in a worksheet formula you actually need to repeat them 4 times. So your formula should look like this:
Hours = "=IF(Last!$B$6>0,(Last!$B$6-Last!$B$5)-SUM(Last!$B$7:$B$10),(IF(Last!$B$5="""","""",$C$1-Last!$B$5-SUM(Last!$B$7:$B$10))))"
I didn't notice that when it was inputting the formula, so it was working, I just didn't have it typed out correctly.
Thank you for that, I'll be sure to double check my coding a bit better.
You're quite welcome. Something that has probably got the best of most of us at one time or another! I know it's happened to me a few times!