Solved

Type Mismatch error Excel VBA

Posted on 2013-12-28
3
762 Views
Last Modified: 2013-12-28
The Do While line keeps giving error message "Type Mismatch".



Sub MostActiveDayPrediction()

    Dim r As Integer
    Dim rng As Excel.Range
    
    Sheets("Stats").Activate
    r = 1
    Set rng = Sheets("Yahoo_Download_2").Range("Results_OEX")
    Do While rng.Offset(r, 0) <> ""
        If rng.Offset(r, 0) = Sheets("Stats").Range("Last_Monday") Then Stop
        r = r + 1
    Loop

End Sub

Open in new window

0
Comment
Question by:rrhandle8
  • 2
3 Comments
 
LVL 80

Expert Comment

by:byundt
Comment Utility
I can reproduce that Type Mismatch error message if rng.Offset(r, 0) contains an error value
If the cell contains a blank, text or a number, it executes fine.
0
 
LVL 80

Accepted Solution

by:
byundt earned 260 total points
Comment Utility
Possible workaround:
Sub MostActiveDayPrediction()

    Dim r As Integer
    Dim rng As Excel.Range
    
    Sheets("Stats").Activate
    r = 1
    Set rng = Sheets("Yahoo_Download_2").Range("Results_OEX")
    Do
        If Not IsError(rng.Offset(r, 0)) Then
            If rng.Offset(r, 0) = "" Then Exit Do
        End If
        If rng.Offset(r, 0) = Sheets("Stats").Range("Last_Monday") Then Stop
        r = r + 1
    Loop

End Sub

Open in new window

0
 

Author Closing Comment

by:rrhandle8
Comment Utility
Thanks, byundt.
I see my mistake.  The range contains multiple rows and columns.  You cannot use .offset when selecting a range that size.  I should have set the range name to just the top left cell.
Thanks for making me think.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now