How to make my own [user-defined function] for min value except 0

I would like to ask you to help me to create my own formulas in VBA

I am a purchaser and my job consist of evaluating the offers from several companies for a certain range of products. From their offers I am choosing the best prices and the supplier where to buy the particular product from that range.

So I need to figure out :
1) Best (Minimum)price
2) Supplier who has the best price for the particular product.

It is an easy formula to set up the minimum price bigger than 0 : =IFERROR(SMALL(range;COUNTIF(range;0)+1);MIN(range))
In my table it is column C =IFERROR(SMALL(G1:J1;COUNTIF(G1:J1;0)+1);MIN(G1:J1))

When I have minimum price I can look for the name of the company with match and index formulas. In my table it is column E =INDEX($E$2:$H$2;MATCH(C1;E1:H1;0)).

Unfortunately, I do not know how to create my own formulas, so I do not have to think about them and write them all the times. Can somebody help me here what exsactly do I have to write to VBA module when I want to create my own formula for BESTSUPLIER and waht for BESTVALUE

1.
Public Function BESTVALUE()
...................................
End Function


2.
Public Function BESTSUPLIER()
......................
End Function

Sorry for the poor description of the problem but my english is far from good but I hope you will understand what I need. I am attaching the excel file "evaluation" from which you can understand it.

If you already have a formula for this that works, why do you want to write a VBA function for it?

Here's something I wrote up to describe how to put UDF's together (https://www.excelforum.com/tips-and-...uild-udfs.html ). The basics of calling but the VB editor and such are in the link given there.

Both formulas are not exactly the easiest to remember, I have always different range of companie (sometimes 2 sometiems 10 or more )and file is more comlicated than the one I showed. There for I wanted to make my own formula where I change only variables and range.
I edited my excel file showing the real case.

It looks that I have to study a lot of VBA to be able to make this my own formula.

Probably I should add this to the commercial subforum.

Here are two functions that do what you need, generic enough to handle any ranges.

BESTVALUE(range) returns the lowest number greater than zero, error if no positive numbers are found

BESTSUPPLIER(value_range, supplier_range) returns the supplier in supplier_range with the lowest corresponding value greater than zero in value_range

Please Login or Register  to view this content.

Here's another suggestion.

Create a normalised two dimensional database table so that you can analyse your data simply with a Pivot Table. If you do that there's no need for functions, either Regular Excel or UDFs

i.e create a table where each column label is a unique TYPE of data, not the value of a type of data. For instance you'd have one column for Company and in that column record the Company name, probably using a data validation drop down. Other Columns would be Pipe Size, Cost,...and whichever of columns F:P are unique TYPES, - I don't know what your data means so can't comment.