R1C1 Forumla in VBA returning Application-defined error 1004

[dsrt16]

When a user selects "Reedsy/Stripe," I need the cell in row E to give the results of this formula. (The number in the row above *.10) + (((The number in the row above + (the number in the row above *.10)) * 0.029 + 0.030).

This is because the Reedsy fee is 10 percent of their earnings listed in the row above. Then the stripe fee is 0.029 percent + 0.30 cents of their earnings * 10 percent. So if they earned $100. The stripe fees are based on them earning $110.

However, when a user selects "Reedsy/Stripe," instead of getting the answer in column E in a negative since it is an expense, it gives an application-defined error.


Here is the vba code where Sheet1.Range("N48")= Reedsy/Stripe. Setup Page R48C17 = 10 percent. Setup Page R44C17 = 0.029 and Setup PAge R44C19 = 0.30.

Why am I getting an error?

Please Login or Register  to view this content.

I feel like I have all the parenthesis right. I tested it on a calculator putting in (100 * .10) + (((100 + (100 * .10)) * 0.029 +0.030) and it gave me the correct answer.

[cubangt]

Have you tried writing the formula into the formula bar as you record it and see if the vba code is the same as yours?

For long formulas like that, i personally find it easier and faster to records a macro, write the formula into the formula bar and stop the recording then test it out

[MrShorty]

Is that an exact copy/paste from your code? I notice that you have correctly paired single quotes around 'Setup Page' in every reference except the last reference, where you neglect the open single quote -- ...R44C17+Setup Page'!R44C19). Should be ..R44C17+'Setup Page'!R44C19)

If that is a typo in your post and the actual code includes that apostrophe character, then you will probably need to dig deeper. The formula works for me with that correction.

[Fluff13]

As well as the missing apostrophe pointed out by MrShorty, you also see to be missing a closing bracket somewhere

[dsrt16]

Thank you. I did have a missing single quote. I fixed that, but I still get an error.

[dsrt16]

Fluff13,

I don't see a missing closing bracket. The only brackets are around the -1. And everytime I have R[-1]. So the bracket is always closed. If you meant parenthesis, I am not sure where I have a missing parenthesis since it works on a calculator with the exact parenthesis I have.

[Fluff13]

I mean a bracket not a square bracket (I'm English) you have 4 opening brackets but only 3 closing ones

Please Login or Register  to view this content.

[dsrt16]

Ok I added a closing bracket at the end. So there are two closing brackets at the end. Now it isn't giving me an error, but it isn't giving me the right calculation.

The amount in the row above is 55. So it should return -7.55 and instead it is giving me -3.45.

When I enter the exact formula as it is in excel in a caclulator, it gives me -7.55. So not sure how Excel is miscalculating.

Formula. I swtiched the order to the original formula so I could use less brackets: =-((E13+(E13*'Setup Page'!$Q$48))*'Setup Page'!$Q$44+'Setup Page'!$S$44)+(E13*'Setup Page'!$Q$48)

The Math ((55+ (55*.10))*0.029+0.30)+(55*.10) If I enter in exactly that into my calculator, I get the right answer. But Excel isn't giving the right answer.

55+(55*.10) = $60.50. $60.50*0.029+0.30 = $2.05. $55*.10 = $5.50. $5.50 + $2.05 =$7.55

[Fluff13]

It would help if you could upload a sample workbook.

[Fluff13]

Looks like you have got the brackets completely wrong, this gives -7.28

Please Login or Register  to view this content.

[dsrt16]

Well, now my brain just hurts. I typed this in excel: =((55+(55*'Setup Page'!Q48))*'Setup Page'!Q44+'Setup Page'!S44)+(55*'Setup Page'!Q48) and got $7.55 (the correct answer).

And literally it is the same parenthesis as what I have in VBA. The only difference is I put in 55 instead of R[-1]C. But the value in R[-1]C is 55, so it should give me the same thing. But I have to have it be R[-1]C since what row it is referring to willc change.

I was basing the parenthesis on what works in an actual calculator.

[dsrt16]

But -7.28 isn't correct. It should be -7.55.

[dsrt16]

I got it to work. By deleting the - (minus sign) at the front, it gave me $7.55.

Please Login or Register  to view this content.

Now I just need to figure out where to put the minus sign, so I get -$7.55


ETA: I figured it out. "=-(((R[-1]C+(R[-1]C*'Setup Page'!R48C17))*'Setup Page'!R44C17+'Setup Page'!R44C19)+(R[-1]C*'Setup Page'!R48C17))"