pass variable in a formula

[SathishKumar]

Hi,

Say when i run a macro, some value is got as a result.
I need to use that variable in a formula.
Also inside the indirect function.
Is this possible???
Pls reply...

Thanks

[MrShorty]

It is difficult to give a specific suggestion in a case like this without an actual sample of what you want.

In general terms, it seems to me that the easiest way might be to have your macro write its result to a cell. Then have the other spreadsheet formulas that need this result refer to that cell.

If the macro can be converted to a user-defined function (a function procedure that can be called from a spreadsheet cell), then you can nest the udf inside of other functions -- just like native excel functions can be nested inside of other functions. =7*myudf(A1,B1) or =IF(A1=0,0,myudf(a1,b1)).

[SathishKumar]

see, suppose the code has two string variables.
str1 = A
str2 = B
these values are got run time.
say i need cell A4 to be updated with a formula =(str1)9 + (str2)10
where originally it should be =A9 + B10

Does this clears your doubt..


thank you,
satz

[MrShorty]

It give a little more detail. There are many different ways to accomplish this kind of thing. Approaching it like my first general suggestion, we first use the .value property (http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx) to assign these strings to a cell.

Please Login or Register  to view this content.

Then you can use the =CONCATENATE() function http://office.microsoft.com/en-us/ex...085.aspx?CTT=1 to build each cell reference inside of an =INDIRECT() function http://office.microsoft.com/en-us/ma...778.aspx?CTT=1. =INDIRECT(CONCATENATE(D2,"9"))+INDIRECT(CONCATENATE(E2,"10"))

[SathishKumar]

so assigning a variable to a formula directly in not possible???

[MrShorty]

On the contrary, it is very possible, if that is how you want to do it. Again, it can be difficult on this side of the internet to provide specific solutions without a good idea of context (usually this means attaching a sample spreadsheet so we can see the context for the request)

In that case, you first use VBA's text operators (concatenate & operator in particular http://msdn.microsoft.com/en-us/libr.../gg264104.aspx) to build the formula as a text string. Then you can assign this text string to the formula property of the cell. http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

Please Login or Register  to view this content.