Type Mismatch error Excel VBA

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

rrhandle8Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
byundtCommented:
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
 
rrhandle8Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.