Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    22

    Unanswered: Runtime error 13 'Type mismatch'

    Hi all,

    Can anyone tell me why this code returns the above runtime error today when it didn't yesterday?

    It's a userform in excel which inputs data into various places within the book.

    Code:
    'Update values into Spreadsheet from userform
    Private Sub Update_Click()
    Range("B3").Select
    Do Until Selection.Value = "" ' Stops the loop when cell is empty
            If Selection.Value <> Date Then 'finds current date
                Selection.Offset(1, 0).Select
                Else
                'Enters values from userform into "Breakdown" sheet
                Selection.Copy
                Selection.Offset(0, 1).Value = Dandy1104_AIN.Value
                Selection.Offset(0, 2).Value = Dandy1104Produced.Value
                Selection.Offset(0, 3).Value = Dandy1104DandyDispatched.Value
                'Add Textbox values as comments
                Dim r As String
                Dim s As String
                Dim t As String
                Dim u As String
                Dim v As String
                Dim x As String
                Dim y As String
                Dim z As String
                r = DOR1.Value
                s = DOR2.Value
                t = DOR3.Value
                u = DOR4.Value
                v = COR1.Value
                x = COR2.Value
                y = COR3.Value
                z = COR4.Value
                
                '* Add Comment:
                    With ActiveCell.Offset(0, 3)
                        .AddComment ("Orders:" & Chr(10) & r & Chr(10) & s & Chr(10) & t & Chr(10) & u)
                        .Comment.Visible = False
                        .Comment.Shape.AutoShapeType = msoShapeRectangle
                    End With
                ActiveCell.Select
                Selection.Offset(0, 4).Value = DandyRejects.Value
                Selection.Offset(0, 5).Value = DandyRejectsReturned.Value
                Selection.Offset(0, 6).Value = Conf1201_AIN.Value
                Selection.Offset(0, 7).Value = Conf1201Produced.Value
                Selection.Offset(0, 8).Value = Conf1201Dispatched.Value
                'Add Textbox values as comments
                '* Add Comment:
                    With ActiveCell.Offset(0, 8)
                        .AddComment ("Orders:" & Chr(10) & v & Chr(10) & x & Chr(10) & y & Chr(10) & z)
                        .Comment.Visible = False
                        .Comment.Shape.AutoShapeType = msoShapeRectangle
                    End With
                ActiveCell.Select
                Selection.Offset(0, 9).Value = ConfRejects.Value
                Selection.Offset(0, 10).Value = ConfRejectsReturned.Value
                Selection.Offset(0, 11).Value = EmptyDIn.Value
                Selection.Offset(0, 12).Value = EmptyCHEPOut.Value
                Selection.Offset(0, 13).Value = DandyTraysIn.Value
                Selection.Offset(0, 14).Value = ConfTraysIn.Value
                'Enters values into "Stocktake" sheet
                Sheet3.Range("I9") = Dandy1104_AIN.Value
                Sheet3.Range("K7") = Dandy1104Produced.Value
                Sheet3.Range("J7") = Dandy1104DandyDispatched.Value
                Sheet3.Range("I17") = Conf1201_AIN.Value
                Sheet3.Range("K15") = Conf1201Produced.Value
                Sheet3.Range("J15") = Conf1201Dispatched.Value
                Sheet3.Range("I23") = EmptyDIn.Value
                Sheet3.Range("J25") = EmptyCHEPOut.Value
                Sheet3.Range("E7") = Dandy1104Count.Value
                Sheet3.Range("E9") = Dandy1104_ACount.Value
                Sheet3.Range("E15") = Conf1201Count.Value
                Sheet3.Range("E17") = Conf1201_ACount.Value
                Sheet3.Range("E23") = EmptyDCount.Value
                Sheet3.Range("E25") = EmptyCHEPCount.Value
                Sheet3.Range("E12") = DandyTraysCount.Value
                Sheet3.Range("E20") = ConfTraysCount.Value
                Sheet3.Range("F3").PasteSpecial (xlPasteValues)
                
                'Clears form after update
                Dandy1104_AIN.Value = ""
                Dandy1104Produced.Value = ""
                Dandy1104DandyDispatched.Value = ""
                DOR1.Value = ""
                DOR2.Value = ""
                DOR3.Value = ""
                DOR4.Value = ""
                COR1.Value = ""
                COR2.Value = ""
                COR3.Value = ""
                COR4.Value = ""
                DandyRejects.Value = ""
                DandyRejectsReturned.Value = ""
                Conf1201_AIN.Value = ""
                Conf1201Produced.Value = ""
                Conf1201Dispatched.Value = ""
                ConfRejects.Value = ""
                ConfRejectsReturned.Value = ""
                EmptyDIn.Value = ""
                EmptyCHEPOut.Value = ""
                Dandy1104Count.Value = ""
                Dandy1104_ACount.Value = ""
                Conf1201Count.Value = ""
                Conf1201_ACount.Value = ""
                EmptyDCount.Value = ""
                EmptyCHEPCount.Value = ""
                DandyTraysCount.Value = ""
                DandyTraysIn.Value = ""
                ConfTraysCount.Value = ""
                ConfTraysIn.Value = ""
                Application.CutCopyMode = False
                Exit Sub
            End If
    Loop
    End Sub
    Any help is appreciated.
    Cheers,
    Dan

  2. #2
    Join Date
    Mar 2010
    Posts
    22
    Nevermind I figured it out.
    Thnx

  3. #3
    Join Date
    Jul 2010
    Location
    Winnipeg, Canada
    Posts
    7
    Quote Originally Posted by danerida View Post
    Nevermind I figured it out.
    Thnx
    You might want to post your solution for anyone who is searching for a similar problem.

  4. #4
    Join Date
    Mar 2010
    Posts
    22
    Quote Originally Posted by R45 View Post
    You might want to post your solution for anyone who is searching for a similar problem.
    Good point ^^^^


    It turns out that the code above was looping through cell in column A looking at the date value. In column B I had the headings for a series of subtotals. I wanted to neaten up the look of the sheet so I merged the subtotal heading cells with the corresponding cells in column A. This meant the code was looping until it reached one of these merged cells and encountered a "text" formatted cell rather than the "date" formatting it was expecting. Hence, the "type mismach".

    Didn't I feel like a dope when I figured that one out!!!!

    Cheers,
    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •