If, ElseIf, End If Problem

[pinkpanther6666]

Hi All,

I am having a problem with the code below, when its run it comes up with error code " Runtime error Code 13" and Type Mismatch, i have looked at the code for a couple of hours or so and i cant see the blatenly obvious. Could someone else have a look and let me know what i have done wrong



Sub InsertData()
Sheets("PSForm").Select
If Range("N5") = "AA" Or "BB" Or "CC" Or "DD" or "End" Then GoTo Line1 Else GoTo Line2
Line1:

If Range("N5").Value = "AA" Then
Sheets("Fixtures").Select
Range("A2:B23").Copy
Sheets("PSForm").Select
Range("V13").Select
ActiveSheet.Paste

ElseIf Range("N5").Value = "BB" Then
Sheets("Fixtures").Select
Range("D2:E23").Copy
Sheets("PSForm").Select
Range("V13").Select
ActiveSheet.Paste

ElseIf Range("N5").Value = "CC" Then
Sheets("Fixtures").Select
Range("G2:H23").Copy
Sheets("PSForm").Select
Range("V13").Select
ActiveSheet.Paste

ElseIf Range("N5").Value = "DD" Then
Sheets("Fixtures").Select
Range("J2:K23").Copy
Sheets("PSForm").Select
Range("V13").Select
ActiveSheet.Paste

ElseIf Range("N5").Value = "End" Then GoTo Line2
Sheets("Fixtures").Select
Range("U1").Copy
Sheets("PSForm").Select
Range("V13:W34").Select
ActiveSheet.Paste

End If

Line2:

Calculate

End Sub



Many Thanks


Steve

 

[MrKowz]

Try to avoid using labels, they cause a lot of problems in general. Here, a Select Case statement would help out:

Public Sub InsertData()
With Sheets("PSForm")
    Select Case .Range("N5").Value
        Case "AA"
            Sheets("Fixtures").Range("A2:B23").Copy Destination:=.Range("V13")
        Case "BB"
            Sheets("Fixtures").Range("D2:E23").Copy Destination:=.Range("V13")
        Case "CC"
            Sheets("Fixtures").Range("G2:H23").Copy Destination:=.Range("V13")
        Case "DD"
            Sheets("Fixtures").Range("J2:K23").Copy Destination:=.Range("V13")
        Case "End"
            Application.Calculate
            Sheets("Fixtures").Range("U1").Copy Destination:=.Range("V13:W34")
        Case Else
            Application.Calculate
    End Select
End With
End Sub

 

[repairman615]

Hello,

Have a look at the IF near the top using the OR(s):

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InsertData()<br>Sheets("PSForm").Select<br><SPAN style="color:#00007F">If</SPAN> Range("N5") = "AA" <SPAN style="color:#00007F">Or</SPAN> Range("N5") = "BB" <SPAN style="color:#00007F">Or</SPAN> Range("N5") = "CC" _<br>        <SPAN style="color:#00007F">Or</SPAN> Range("N5") = "DD" <SPAN style="color:#00007F">Or</SPAN> Range("N5") = "End" Then _<br>                <SPAN style="color:#00007F">GoTo</SPAN> Line1 <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Line2<br>Line1:<br><br><SPAN style="color:#00007F">If</SPAN> Range("N5").Value = "AA" <SPAN style="color:#00007F">Then</SPAN><br>Sheets("Fixtures").Select<br>Range("A2:B23").Copy<br>Sheets("PSForm").Select<br>Range("V13").Select<br>ActiveSheet.Paste<br><br><SPAN style="color:#00007F">ElseIf</SPAN> Range("N5").Value = "BB" <SPAN style="color:#00007F">Then</SPAN><br>Sheets("Fixtures").Select<br>Range("D2:E23").Copy<br>Sheets("PSForm").Select<br>Range("V13").Select<br>ActiveSheet.Paste<br><br><SPAN style="color:#00007F">ElseIf</SPAN> Range("N5").Value = "CC" <SPAN style="color:#00007F">Then</SPAN><br>Sheets("Fixtures").Select<br>Range("G2:H23").Copy<br>Sheets("PSForm").Select<br>Range("V13").Select<br>ActiveSheet.Paste<br><br><SPAN style="color:#00007F">ElseIf</SPAN> Range("N5").Value = "DD" <SPAN style="color:#00007F">Then</SPAN><br>Sheets("Fixtures").Select<br>Range("J2:K23").Copy<br>Sheets("PSForm").Select<br>Range("V13").Select<br>ActiveSheet.Paste<br><br><SPAN style="color:#00007F">ElseIf</SPAN> Range("N5").Value = "End" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Line2<br>Sheets("Fixtures").Select<br>Range("U1").Copy<br>Sheets("PSForm").Select<br>Range("V13:W34").Select<br>ActiveSheet.Paste<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>Line2:<br><br>Calculate<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[pinkpanther6666]

Wow

Very quick response

Many Many thanks


Steve

 

[Ruddles]

If Range("N5") = "AA" Or "BB" Or "CC" Or "DD" or "End" Then GoTo Line1 Else GoTo Line2

If I can explain why this statement is invalid, it might help you avoid the same mistake in the future.

The way you have written it, VBA thinks you mean:-

If Range("N5") = "AA" is TRUE Then GoTo Line1
Or
If "BB" is TRUE Then GoTo Line1
Or
If "CC" is TRUE Then GoTo Line1
Or
If "DD" is TRUE Then GoTo Line1
... etc.

Range("N5") = "AA" is an expression which can be either TRUE or FALSE, so VBA is happy with that.

"BB" is not an expression which can be either TRUE or FALSE - it is a string of letters - so VBA reports an error.

Repairman's solution takes those invalid "BB", "CC", etc, and turns them into expressions which can be individually evaluated as TRUE or FALSE.

You were fortunate that you were comparing strings - if you'd made the same mistake with numeric values, VBA would probably never have reported an error and you would have been thoroughly confused.

As a small test, run this:-

sub testproc()
if 1 = 2 or 3 then msgbox "1 equals 2 or 3"
end sub

Was it what you were expecting?

Now try this:-

sub testproc()
if 7 = (3 or 4) then msgbox "7 equals 3 or 4"
end sub

You have to be very careful what you tell VBA to do because it will do exactly what you tell it rather than what you think you meant to tell it!