Thread: Converting formula to UDF

Try this:

Function MyConvert(sMeas As String, dAmt As Double, nServ As Long) As String
    Dim dExt      As Double     ' extended amount
    Dim nLB       As Long       ' number of lb
    Dim nOz       As Long       ' number of oz

    dExt = nServ * dAmt

    Select Case LCase(sMeas)
        Case "weight"
            ' assumes dAmt is in oz
            nLB = dExt \ 16
            nOz = dExt - 16 * nLB
            
            If nLB Then MyConvert = Format(nLB, "0 lb")
            If nOz Then MyConvert = LTrim(MyConvert & Format(nOz, " 0 oz"))

        Case "volume"
            MyConvert = "???"

        Case Else
            MyConvert = "???"
    End Select
End Function

E.g.,

      --B--- -C- -----D------ ------------------E------------------
  2    nServ   6                                                   
  3   sMeas  Amt Extended Amt                                      
  4   Weight   3 1 lb 2 oz    D4 and down: =myconvert(B4, C4, $C$2)
  5   Weight   4 1 lb 8 oz                                         
  6   Weight   5 1 lb 14 oz                                        
  7   Weight   6 2 lb 4 oz                                         
  8   Weight   7 2 lb 10 oz                                        
  9   Weight   8 3 lb