Runtime error 13, type mismatch when multiplying empty value cell with a value from a Name

[I_Batman]

Hi again.

I am trying to create a Workbook Event.
I am having issues when I try to multiply cell E2 by a value in the Name "Duty".

The line in the code that is hanging me up is

Cells(2, 6).Value = Cells(2, 5).Value * Names("Duty").Value

The only thing I can envision is that I have not defined the cell E2 as anything. Could that be the problem, or an I missing some very basic syntax?

The entire code so far (will be cleaned up, and variables made worksheet specific, eventually)

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Names.Add Name:="Duty", RefersTo:=0.06
 Names.Add Name:="HST", RefersTo:=0.13
 Names.Add Name:="Profit", RefersTo:=0.12
 Names.Add Name:="Freight", RefersTo:=0.36
 
 Sh.Name = "Test1"
 
 Cells(1, 1).Value = "Description"
 Cells(1, 2).Value = "Plank Type"
 Cells(1, 3).Value = "Sq.Ft/Pack"
 Cells(1, 4).Value = "In Stock"
 Cells(1, 5).Value = "$/Sq.Ft."
 Cells(1, 6).Value = "Duty"
 Cells(1, 7).Value = "HST"
 Cells(1, 8).Value = "Markup"
 Cells(1, 9).Value = "Freight"
 Cells(1, 10).Value = "Subtotal"
 
 Cells(2, 6).Value = Cells(2, 5).Value * Names("Duty").Value
 
 Rows("1:1").Font.Bold = True
 Columns("I:I").NumberFormat = "$#,##0.00"
 
 Cells.Columns.AutoFit
 
End Sub

 

[shg]

Since you'd defined a name, why not use it?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Names.Add Name:="Duty", RefersTo:=0.06
    Names.Add Name:="HST", RefersTo:=0.13
    Names.Add Name:="Profit", RefersTo:=0.12
    Names.Add Name:="Freight", RefersTo:=0.36
 
    With Sh
        .Name = "Test1"
 
        With .Range("A1:J1")
            .Value = Array("Description", "Plank Type", "Sq.Ft/Pack", "In Stock", "$/Sq.Ft.", _
                           "Duty", "HST", "Markup", "Freight", "Subtotal")
            .Font.Bold = True
            .EntireColumn.AutoFit
        End With
 
        .Range("F2").FormulaR1C1 = "=Duty * rc[-1]"
        .Columns("I:I").NumberFormat = "$#,##0.00"
    End With
End Sub

 

[I_Batman]

Thanks for the cleaning of the code, shg
Yours does look so much better than mine.

But what was the fundamental problem with my original line of code that created the runtime error?

 

[xenou]

Names don't have value. You are using a syntax here that applies to Cells, not Names.
Cells(2, 6).Value = Cells(2, 5).Value * Names("Duty").Value

shg's advice is good (this gets to the name's value just as you would in any formula on a worksheet).

To scrape it out in vba alone you can put together some formulation like this (probably not best - I just mashed this up -the point is that the refers to property has the equals sign in it so we have to strip it out, though this would crash on a non-numeric named constant):

Debug.Print Val(Replace(ThisWorkbook.Names("Duty").RefersTo, "=", ""))

More simply using a shortcut syntax for evaluate:

Debug.Print [Duty]

Pearson offers an all purpose function on his site:
http://www.cpearson.com/excel/DefinedNames.aspx

Function GetNameRefersTo(TheName As String) As String
    Dim S As String
    Dim HasRef As Boolean
    Dim R As Range
    Dim NM As Name
    Set NM = ThisWorkbook.Names(TheName)
    On Error Resume Next
    Set R = NM.RefersToRange
    If Err.Number = 0 Then
        HasRef = True
    Else
        HasRef = False
    End If
    If HasRef = True Then
        S = R.Text
    Else
        S = NM.RefersTo
        If StrComp(Mid(S, 2, 1), Chr(34), vbBinaryCompare) = 0 Then
            ' text constant
            S = Mid(S, 3, Len(S) - 3)
        Else
            ' numeric contant
            S = Mid(S, 2)
        End If
    End If
    GetNameRefersTo = S
End Function

 

[shg]

A name does have a value property; it's the string containing the formula that the name refers to. Like all Excel formulas, it begins with an equals sign, which causes the error.

Debug.Print Val(Replace(ThisWorkbook.Names("Duty").RefersTo, "=", ""))

I think it would be preferable to use CDbl, which is international-aware, instead of Val.