getting #VALUE error on the cell ith the function

[littleangel]

i keep getting #VALUE! error on the cell...

my code is

Please Login or Register  to view this content.

[hrlngrv]

The cause of the #VALUE! error is that your function throws runtime errors on x.Value.

FIRST, there's absolutely no good reason not to declare C or x along with Result and pc. More fundamentally, there's no good reason not to begin all VBA code modules with Option Explicit to oblige you to declare all variables. Had you done so, you would have discovered that Split(pc, ",") produces an array of strings. You can iterate through arrays with For Each x . . ., but that x loop variable would hold a string value rather than a Range object. In brief, x doesn't have a .Value property.

Next time, when UDFs return #VALUE! errors, add the statement

Stop

just after variable declarations. That will pause execution at the beginning of the function. Then you can step through the function in Debug mode, and the VBA Editor will show you useful error messages as runtime errors occur.

That said, there are problems in your function. Replace all x.Value with x, and your function would only ever return "" or "GREEN" because each of the 3 If blocks inside the For Each x . . . block performs the same test, x = SL. Presumably you mean the 2nd to compare x to ST and the 3rd to SB. Even then, why iterate through all of ANALYSIS!A3:A11? Just use Application.Match to find the bottommost instance of chkpc in ANALYSIS!A3:A11, then use the corresponding cell in ANALYSIS!H3:H11 for the Split(...) call.

Then there's generality and recalculation dependence. As written, if you're using this function in cell formulas, if cell values change in ANALYSIS!A3:A11 or ANALYSIS!H3:H11, cell formulas calling this function won't update via standard (minimal) automatic recalculation because Excel won't realize those formulas depend on those ranges because those ranges aren't function arguments.

[littleangel]

yup it orked...changingx.vaue to x worked...thank u so much