Help with #Value! errors

[ReyCBX]

Howdy all.

New to this site/forum.

My issue is about "hiding" #Value! in cells based on preceding cells used in UDFs or formula calculations that are empty due to being a new row or potentially the user has not entered data into that cell.

Probably should also mention that I'm more knowledgeable with Access, both using and w/VBA than with Excel VBA and using Excel though I keep learning and mange to keep swimming...

My project needs to display 1,000 rows for user to enter the product number in the product number column (column B).

User then clicks on a Retrieve button that hits via ADO an Access 2003 database on the network and retrieves product info and other user entered data for that specific product. I have a button and associated textbox for adding additional rows if needed...

The data rows are appended to the ItemData worksheet and which the main product sheet uses VLookup in several of the columns to populate the respective cells.

So while I have used IFError to "hide" the #Value! or other errors in the VLookup cells, those cells that either calculate via formula or a UDF show #Value!

For example, the Brand/Label column (I) uses

=IFERROR(VLOOKUP(TEXT($B:$B,"0000000"),ItemData!$A$12:$N$4971,3,FALSE),"")

The UDF function BidCost in column X uses

=BidCost(P38,Q38,T38,U38,V38,W38)

and displays #Value!. Research via Google indicated changing incoming parameters to Variant might help - nope.

Thus, Sells Margin Ext (column AC) uses =Z38*E38. E38 is Est. Usage and empty; Z38 (Sells Margin $) contains the formula

=IF(AND(OR(ISBLANK(G38),G38="Y"),LEFT(X38,1)<>"#",Y38>0),Y38-X38,0)

and also displays #Value!

At this point, I also do not know if #Value! being displayed is acceptable to mgmt or even the user
Apologize for the long explanation but I'm getting a flat forehead and need help.

Thanks,
Rey

[SamT]

Upload the workbook after truncating the data and obscuring any personal or proprietary info in it. Use the "Go Advanced" button and look below the Post Editor for "Manage Attachments."

Also paste the entire code of the UDF. The # Icon will insert CODE Tags around the selected/highlighted code.

[ReyCBX]

Howdy Same.

Below is the BidCost UDF.
Will try to cleanup the workbook.

Thanks,
Rey

Option Private Module
Option Explicit


Function BidCost(POCost As Variant, Freight As Variant, VendFundType As String, VendAmt As Double, _
                InternalFundType As String, InternalAmt As Double) As Variant


' 5/31/2016, Rey, changed to variant as way of not returning #Value! on an empty row; from internet suggestion read...
'Function BidCost(POCost As Variant, Freight As Variant, VendFundType As Variant, VendAmt As Variant, _
                InternalFundType As Variant, InternalAmt As Variant) As Variant


    On Error GoTo eh_BidCost
           
    If VendFundType = "-" Then VendFundType = ""
    If InternalFundType = "-" Then InternalFundType = ""


    Select Case VendFundType
    Case Is = ""
        Select Case InternalFundType
        Case Is = ""
            BidCost = "#Funding"
        Case Is = "A"
            BidCost = POCost - InternalAmt
        Case Is = "D"
            BidCost = Application.Min(POCost, InternalAmt)
        Case Else
            BidCost = "#Error"
        End Select
    Case Is = "A"
        Select Case InternalFundType
        Case Is = ""
            BidCost = POCost - VendAmt
        Case Is = "A"
            BidCost = POCost - VendAmt - InternalAmt
        Case Is = "D"
            BidCost = Application.Min(POCost, InternalAmt) - VendAmt
        Case Else
            BidCost = "#Error"
        End Select
    Case Is = "L"
        Select Case InternalFundType
        Case Is = ""
            BidCost = POCost
        Case Is = "A"
            BidCost = POCost - InternalAmt
        Case Is = "D"
            BidCost = "#Error"
        Case Else
            BidCost = "#Error"
        End Select
    Case Is = "GD"
        Select Case InternalFundType
        Case Is = ""
            BidCost = VendAmt
        Case Is = "A"
            BidCost = VendAmt - InternalAmt
        Case Is = "D"
            BidCost = "#Error"
        Case Else
            BidCost = "#Error"
        End Select
    Case Is = "GF"
        If Freight <= 0 Then GoTo eh_BidCost
        Select Case InternalFundType
        Case Is = ""
            BidCost = VendAmt + Freight
        Case Is = "A"
            BidCost = VendAmt + Freight - InternalAmt
        Case Is = "D"
            BidCost = "#Error"
        Case Else
            BidCost = "#Error"
        End Select
    Case Else
        BidCost = "#Error"
    End Select
    If IsNumeric(BidCost) Then
        If BidCost < 0 Then BidCost = "#Negative"
    End If
    Exit Function
eh_BidCost:
    BidCost = "#Error"
End Function

[ReyCBX]

Apologies Sam not Same - fat fingers.

Rey

[ReyCBX]

Seems I just resolved the BidCost UDF #Value! display as the cell displayed nothing using

=IF(ISERROR(BidCost(P38,Q38,T38,U38,V38,W38)), "", BidCost(P38,Q38,T38,U38,V38,W38))

And doing this it also hid the #Value! in the Sells Margin $ column while the Sells Margin Ext now displays $0.00 (formatted as Accounting).

Could have sworn I enclosed the BidCost UDF using IF but it may have been just ISError - too close to the trees to see the forest 8-)

Let's see how this goes.

Thanks,

Rey