[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Run-time error '13'

Posted on 2011-09-02
7
Medium Priority
?
184 Views
Last Modified: 2012-06-21
Hello Experts,

Can someone please tell me why I keep on getting a Run-time error, Type mismatch when I open my spreadsheet with the following code. When click on debug it highlights the following:

 If Stocks(i) <> .Value Then




Private Sub Worksheet_Calculate()
Dim cel As Range
Dim Addr As Variant, Targ As Variant
Static Stocks(3) As Double      'Starts with element 0
Dim i As Long, n As Long
Addr = Array("AQ3", "AV3", "AP3", "AU3")    'Watch these cells for price changes
Targ = Array(85, 85, 20, 20)                'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
    With Range(Addr(i))
        If Stocks(i) <> .Value Then
            Stocks(i) = .Value
            If .Value > Targ(i) Then
                Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
                Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
                Close 1
            End If
        End If
    End With
Next
End Sub

Open in new window



Cheers
0
Comment
Question by:cpatte7372
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 

Author Comment

by:cpatte7372
ID: 36472608
For the Experts willing to assist the attached spreadsheet my provide further assistance.

Cheers
EE-TypeMismatch.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36472762
It's because AU3 contains a #DIV/0! error and therefore is not numeric. You coulod add a check to your code as follows:
Private Sub Worksheet_Calculate()
Dim cel As Range
Dim Addr As Variant, Targ As Variant
Static Stocks(3) As Double      'Starts with element 0
Dim i As Long, n As Long
Addr = Array("AQ3", "AV3", "AP3", "AU3")    'Watch these cells for price changes
Targ = Array(85, 85, 20, 20)                'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
    With Range(Addr(i))
        If Not IsError(.Value) Then
            If Stocks(i) <> .Value Then
                Stocks(i) = .Value
                If .Value > Targ(i) Then
                    Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
                    Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
                    Close 1
                End If
            End If
        End If
    End With
Next
End Sub

Open in new window


0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36472769
Actually you might also want to check that it is numeric, as a non-numeric value would also cause your code to fail.  You could add an IF statement inside the error check like:

If Isnumeric(.Value) Then  .....
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cpatte7372
ID: 36472841
Andrew,

You're code seemed to have done the trick cheers mate
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 36472862
Good - happy to help... but I'd still like the points (and for the conditional formatting one I answered for you earlier)  ;-)
0
 

Author Closing Comment

by:cpatte7372
ID: 36519710
Hi sorry for the delay in awarding points.

Cheers mate.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question