Calling a user Function in Worksheet

[rameshs319]

Hi....

I am creating an application wherein the data's are present in column
A. Based on the entries in column A, there are a set of formulas to
calculate the result.

i created functions to do this operation. "Line_ID_mm" is one such
function.
When calling this function in worksheet as per the below procedure, it
returns an error #Value.

when executing calculation steps, it doesn't read the value in column
C3, as the Excel function Vlookup reads and returns the required.

What needs to be done in the function to get it resolved.

See the code below:

For calling the Function:

Set proj_sht1 = Workbooks("sample.xls").Sheets("test")
n = Application.CountA(proj_sht1.Range("A:A")) - 1

proj_sht1.Range("O3:O" & n).Formula =
"=Line_ID_mm(VLOOKUP(B3,linesize_in_mm,2,FALSE),C3)"

Function:

Function Line_ID_mm(Line_size As Integer, Line_Sch As Variant) As
Single

Dim row_num As Integer

row_num = WorksheetFunction.Match(Line_size & Line_Sch,
Range("Sch_num"), 0)
Line_ID_mm = WorksheetFunction.Index(Range("pipe_id_num"), row_num, 8)

End Function

Please advice

S.Ramesh

 

[Joel]

I would break the problem into two parts. first check that the formula is
correct on the worksheet. Click cell O3 and check that the formula is
correct (look at fx box). If this is correct then set a break point in the
function Line_ID_mm. Click on first line of code and press F9 in VBA window.
Then go back to worksheet and click on cell O3. Then go to fx box and
click on end of formula and press Enter on Keyboard.

The first line of code in the VBA window should know be highlight. You can
add each of the parameters of the function (Line_size and Line_Sch ) to the
watch window by highlighting these variables with the mouse and right click.
then select add to watch. You can also step through the code by pressing F8.