Replace formula with UDF

**max57** · 01-14-2011, 07:52 PM

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(IngredientStart,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(IngredientStart,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(IngredientStart,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

**royUK** · 01-15-2011, 05:20 AM

In Excel you can define names that refer to specific cells or ranges of cells in a workbook. In the same way (using Insert | Name | Define), you can assign a formula to a name, and then use that name in place of the formula throughout the workbook.

**max57** · 01-15-2011, 12:09 PM

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.