PDA

View Full Version : Get Formula



willemeulen
12-07-2009, 05:13 AM
Still new in VBA and only learning it piece by piece:banghead: . I picked up old excel project of mine and just playing around to get some VBA working. Picked two items I want to achieve which I think are usefull in other sheets and give me the first baby steps how VBA works.

Firt one is GET FORMULA

Got the following VBA

Function GetFormula(Cell as Range) as String
GetFormula = Cell.Formula
End Function

works when typing into cell

=GetFormula(A1)

Returns the actual formula in writing

Like =A3*B3

But want to modify the VBA to get it working when I use a vlookup instead of dircet reference to cell and I want it to return the actual formula working (I have a Lookup table which contains formula's using the Row() as refferene in formula, so formula works on other sheet. Formula's can either be noted with or without equal sign in front. To be clear the formulas in the lookup tabel actually don't give any usefull answer but when transferred/coppied to destination cell (other sheet) the input value's are in fixed columns.

Any help in welcome:help .


Cheers

Bob Phillips
12-07-2009, 05:40 AM
Give us an example of the formula in the cell and what you would expect your function to return.

willemeulen
12-07-2009, 06:43 AM
Sheet 1
I have a whole bunch of rows in column G which need to use a formula based on a value in column K.

For the formula's there are 33 different options, they are written in a lookup table on other sheet (A3:B36). The formula's (column B) use fixed columns and "ROW()" to make them usefull on sheet one.

The input values in sheet on can be found from column L - P and more

Example:

in Row whatever on sheet 1 I update column K to code 53; lookup table shows its L&Row()*M&ROW()/N&Row()

Now in Column G "53" is used in the lookup table and uses/copy's the formula found

Could be the macro I have now is the wrong way to go?! dunno about that.

w

Bob Phillips
12-07-2009, 07:01 AM
I can't see that that answers the question that I asked, so I still don't know what should be returned.

willemeulen
12-07-2009, 12:23 PM
What should be returned is a formula found in the lookup table

in this case that can be 33 different formulas depending on the input found in column K

In the previous post I actually showed wrong type of formula, not that is matters but references will be like
=Indirect ("L"&ROW())*Indirect("M"&ROW()) etc