PDA

View Full Version : Help needed with this function call



Lbis82
10-01-2008, 10:38 PM
Function fn_example(varName, varMonth)
Dim varRow1, varRowName1

varRow1 = Application.WorksheetFunction.Match(varName, Worksheets(1).Range("B1:B1000"), 0)
varRowName1 = "B" & CStr(varRow1)
Range("A2").Text = "hello" 'in sheet 2
fn_example = varRowName1

End Function

The above function works like this:
In sheet 1, cell B5 contains the text "Ben" and in sheet 2, A1 contains the formula "=fn_example("Ben", "January")". varMonth is not used in this example btw. When executed, it returned a #value error. Why is this so? I have only utilized a simple range function which I do not see anything wrong with it but the debugger always stops at this line. Can someone please care to advise? Thank you.

p/s: the varRow1 is a no. representing which row "Ben" is in sheet 1 column B.

Bob Phillips
10-02-2008, 12:15 AM
You cannot set values or attributes in cells from within a UDF, you can only return a result to the calling cell (A1 in this case).

What are you trying to do?

GTO
10-02-2008, 12:17 AM
Function fn_example(varName, varMonth)
Dim varRow1, varRowName1

varRow1 = Application.WorksheetFunction.Match(varName, Worksheets(1).Range("B1:B1000"), 0)
varRowName1 = "B" & CStr(varRow1)

'// This line errors no matter what. You can retrieve .Text but not set it.//
'Range("A2").Text = "hello" 'in sheet 2

'//It would normally be like this...
'Sheet2.Range("A2").Value = "hello" 'in sheet 2
'... but I do not believe you can call a UDF from a sheet and have it change values of other cells. With
'this rem'd out though, "B5" is returned to cell A1 in your example.//

fn_example = varRowName1

End Function

Lbis82
10-02-2008, 06:05 PM
Thank you, xld/GTO for your prompt help. I din know that udf cannot change values of other cells, which that was what I was trying to do, something like a simultaneous update. I have now changed to using a button to do the job. =) Thanks...