Thread: Replace formula with UDF

Hi Folks,

I had noticed an earlier thread referring to the creation of a UDF.
I have created UDF's on simpler, much less lengthy formulas, but I'd like to know if there's an easier method when applying it to a formula, such as listed below?

Is there another avenue other than writing Application.WorksheetFunction for each instance of MATCH?

Also, how would these nested formulas actually transpose to VBA, if its even possible at all to do so?

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=IF(OR($D10="",$E10=""),"",

(IF(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),1,1,1)="ml",
((OFFSET((OFFSET(IngredientStart,MATCH($C10,IngredientColumn,),0,1,1)),0,(MATCH($E10,(OFFSET(Ingredi entStart,MATCH($C10,IngredientColumn,0),2,1,3)),0))+4,1,1))*mlPerOz)
*$D10/INDEX(Pur_Unit_WT.oz,MATCH($C10,IngredientColumn,0)) ,

(IF(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),1,1,1)="L",
((OFFSET((OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),0,1,1)),0,(MATCH($E10,(OFFSET(Ingred ientStart,MATCH($C10,IngredientColumn,0),2,1,3)),0))+4,1,1))*LperOz)
*$D10/INDEX(Pur_Unit_WT.oz,MATCH($C10,IngredientColumn,0)) ,

(OFFSET((OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),0,1,1)),0,(MATCH($E10,(OFFSET(Ingredie ntStart,MATCH($C10,IngredientColumn,0),2,1,3)),0))+4,1,1))
*$D10/INDEX(Pur_Unit_WT.oz,MATCH($C10,IngredientColumn,0)))))) )

+++++++++++++++++++++++++++++++++++++++++++++++++

This is all one formula and it works quite nicely.
There are only three cells being referenced.
Is it possible to create a UDF along this line: ‘ =Ingredient(Cell 1, Cell 2 ,Cell 3) ‘
All of the Range References are on the “IngredientsList” sheet


Thanks for your time.
Mark

Hi Roy,

I have used that method on occasion for simpler calculations; but in this case, in which the formula is residing in a number of rows, I'm not certain that this is the appropriate solution.

The formula is in a number of rows in Column F
The formula in each row of F, references Columns C,D, and E.
ex. Cell F3's formula references cells C3, D3 and E3.
Cell F4's formula references cells C4, D4 and E4.
I played with using the defined formula method and had no luck.


I was thinking more along the lines of a VBA User Defined Function that would use variables as place holders for the cell values.

This relates back to my question of using Application.WorksheetFunction.
Looking at the complex formula, is there a way to rewrite this in VBA so that it may be called as a UDF in each cell?

I've never seen something done in this manner and I'm not sure how to do it with all the MATCH and OFFSET functions involved.

can you post a sample workbook with data and the formula in use?