Type Mismatch arises when adding a basic loop in VBA

Question

I have a basic If ElseIf statement I'm trying to loop over a few hundred rows. The If/Else statement itself works until I try and Loop it (which I've included below). When I run it, it gives me a run time error "13" -type mismatch. I Initially set MyCell as a String until I had this error arise. Then I figured setting MyCell as a Variant I would be able to avoid this situation but it's still returning the RTE 13.

Sub code_reassign()
Dim Count As Integer
Dim MyCell As Variant

Count = 1

Do While Count < 10
    MyCell = ActiveCell.Value

    If MyCell = "Busycotypus canaliculatus" Then
        ActiveCell.Offset(0, -1).Value = "N106"
    ElseIf MyCell = "Busycon carica" Then
        ActiveCell.Offset(0, -1).Value = "N104"
    ElseIf MyCell = "Busycon perversum" Or "Busycon sinistrum" Then
        ActiveCell.Offset(0, -1).Value = "N103"
    ElseIf MyCell = "Busycotypus spiratus" Then
        ActiveCell.Offset(0, -1).Value = "N107"
    Else
    End If

    ActiveCell.Offset(1, 0).Select
    Count = Count + 1

Loop

End Sub

I'm still super new to VBA but have been thrown in the deep end at work. I'm doing what I can and studying the basics at home at nights to try and catch up. Any insight as to why the loop is creating a problem with mismatching would be highly appreciated.

score 1 · Answer 1 · edited May 23 '17 at 12:22

1

Change

ElseIf MyCell = "Busycon perversum" Or "Busycon sinistrum" Then

to

ElseIf MyCell = "Busycon perversum" Or  MyCell = "Busycon sinistrum" Then

You need the full expression when using Or.

Since you're new and using .Select this might be a good read for you when you have time: How to avoid using Select in Excel VBA macros

edited May 23 '17 at 12:22

Community

1
1

answered Mar 25 '15 at 17:05

Sobigen

2,038
15
23

Thank you very much. That solved the issue! I will absolutely check out that link. Trying to soak up as much knowledge as I can. – Crooks Mar 27 '15 at 12:44

score 0 · Answer 2 · answered Mar 25 '15 at 17:10

0

@Sobigen's answer is a good one but if the looping is failing my hunch is Count is a reserved word. Change the variable Count to something else.

answered Mar 25 '15 at 17:10

Bmo

1,212
11
34

Thanks for the heads up about reserved words, I changed it just in case as well as fixing the Or expression and it seems to work now. Still getting my feet on the ground and am learning a lot from just this one snippet of code. – Crooks Mar 27 '15 at 12:51

score 0 · Answer 3 · answered Mar 25 '15 at 18:32

Sobigen's answer should address the Mismatch error. Here is some help to clean up this code a bit. Whenever I have more than 2 or 3 conditions, I find Select Case to be preferable to If/ElseIf/ElseIf. Your mileage may vary, but I find it is easier to read and interpret, especially when you have Or conditions, the Case switch will allow multiple values.

Also, since you have a known loop, there's no reason to use Do While instead of For ... Next

Sub code_reassign()
Dim i As Integer
Dim MyCell As Range

Set MyCell = ActiveCell

For i = 1 to 10
    Select Case MyCell.Value
        Case "Busycotypus canaliculatus"
            MyCell.Offset(0, -1).Value = "N106"
        Case "Busycon carica"
            MyCell.Offset(0, -1).Value = "N104"
        Case "Busycon perversum", "Busycon sinistrum" 
            MyCell.Offset(0, -1).Value = "N103"
        Case "Busycotypus spiratus"
            MyCell.Offset(0, -1).Value = "N107"
    End Select

    Set MyCell = MyCell.Offset(1, 0)

Loop

End Sub

Thanks for the input! I'm fairly new, I used a basic layout that I had seen on another script a co-worker wrote but I'll definitely be reading into select case usage. I'm not quite sure what you mean by a Known loop, for testing purposes I used while < 10 but once I get the kinks worked out i'll be using the special cells method to find the last used row in the Sheet since each sheet will have upwords of 30,000 entries. — Crooks, Mar 27 '15 at 12:47
Do While loop is most commonly used when you don't know (at design time) when the loop should terminate. A fixed start/end for the loop, like yours, is usually done with a For/Next loop. — David Zemens, Mar 27 '15 at 12:53

Type Mismatch arises when adding a basic loop in VBA

3 Answers3