Parse a static value from within a formula?

[MCunningham]

I'd like to parse a static value out of a cell's formula and assign it to a variable, let's call it fee rate. While I can't post the document I'm working with, I can give some of the relevant details:

CjFjC2R.png

In that cell C1, there is a formula that calculates the fee based on the transaction count, factored by a certain decimal value. I need to assign that rate to a variable and request your help.

Thank you!

[MCunningham]

Bump please.

[MrShorty]

As with a lot of coding problems, break it down into a series of logical steps:

1) You will need to use the .formula property of the range object to gain access to the formula http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

2) Once you have the text string for the formula, you will need to locate the desired constant. Can we assume from your example picture that the constant will always be the last/rightmost element in the formula? If so, maybe use the InStrRev function to locate the asterisk http://msdn.microsoft.com/en-us/libr.../gg251545.aspx

3) Having located where the constant should be in the text string, we need to extract. Again, assuming it is always the right most element of the formula text string, you might use the Right() function http://msdn.microsoft.com/en-us/libr.../gg278801.aspx

4) Depending on what you are going to do with this constant, you may need to convert it from a text string to a number http://msdn.microsoft.com/en-us/library/s2dy91zy.aspx

[MCunningham]

Yes, the value I'm looking for is always a 4 decimal value at the far right of the formula.

Thank you for your tips, however, I'm not seeing how to extract the formula in step 1. I see how to set a formula in a cell, but my knowledge of this code syntax is fairly limited.

[MCunningham]

double post

[StephenR]

Following on from MrShorty:

Please Login or Register  to view this content.

[MrShorty]

Yes, the value I'm looking for is always a 4 decimal value at the far right of the formula.

If this is always always without exception true, then you should not need the "find the asterisk" portion of the routine. The value will always be the 6 rightmost characters of the formula string.

I see how to set a formula in a cell, but my knowledge of this code syntax is fairly limited.

As noted in the help file, the formula property is Read/Write. To read the formula property, simply put it on the right side of the equals sign.

Please Login or Register  to view this content.