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