User defined function not filling down

[BobSullivan]

I have an excel example here with a macro and a User defined function. I've attached an example. The user defined function is in cell B2. It is supplsed to take the information from the cell in column A and parse it to a friendlier view. Seems simple, but when I grab the fill handle and copy down, the cell reference changes in the argument, but the result reflects what is in cell B2. What do I have to change in my function to make it work correctly?

Code: Select all

Function GetProductName(ProdId)
    ProdId = ActiveCell.Offset(0, -1).Value
    numChars = Len(ProdId)
    numToDrop = InStr(ProdId, "_")
    numToKeep = numChars - numToDrop
    Desc = Right(ProdId, numToKeep)
    Desc = Replace(Desc, "_", " ")
    Desc = WorksheetFunction.Proper(Desc)
    GetProductName = Desc

End Function

[HansV]

Remove the line

Code: Select all

    ProdId = ActiveCell.Offset(0, -1).Value

It overwrites the ProdId argument that you specify in the cell formula with the value of the cell to the left of the active cell. That is almost never the cell to the left of the cell with the formula!

After removing that line, switch back to Excel and press F9 to recalculate. Lo, and behold! You'll get the desired result.

[BobSullivan]

Thanks! Worked great.