ElseIf and Or statement in VBA Excel 2010

[Shamos]

Hello,

I'm a new user of VBA and have run into a problem with the (poorly) written code I have made.
I have tried to shorten my code by combining common tasks, however it runs but selects the wrong data
In the example below,

The code returns the expected if I select "M135X" and "300 hrs", but unexpected results if "M135X" and "1500 hrs" is selected

My variables are models and hours - the code below is for one model.

Any assistance would be greatly appreciated

'Next Model
ElseIf Range("D3") = "M135X" And Range("E3") = "1st Service" Then
ThisWorkbook.Worksheets("M100X-135X").Range("B37:D44").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("B36:E41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ElseIf Range("D3") = "M135X" And Range("E3") = "300 hrs" Or Range("E3") = "1500 hrs" Or Range("E3") = "2100 hrs" Then
ThisWorkbook.Worksheets("M100X-135X").Range("Z37:AB43").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("M100X-135X").Range("B47:D50").Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("N36:Q41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ElseIf Range("D3") = "M135X" And Range("E3") = "600 hrs" Or Range("E3") = "1200 hrs" Or Range("E3") = "2400 hrs" Or Range("E3") = "3000 hrs" Then
ThisWorkbook.Worksheets("M100X-135X").Range("AL37:AN45").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("M100X-135X").Range("B47:D50").Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("V36:Y41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ElseIf Range("D3") = "M135X" And Range("E3") = "900 hrs" Or Range("E3") = "1800 hrs" Or Range("E3") = "2700 hrs" Then
ThisWorkbook.Worksheets("M100X-135X").Range("Z37:AB43").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("M100X-135X").Range("B47:D50").Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("N36:Q41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
'Next Model

 

[essentialkiwi]

try making the changes below

'Next Model
ElseIf Range("D3") = "M135X" And Range("E3") = "1st Service" Then
ThisWorkbook.Worksheets("M100X-135X").Range("B37:D44").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("B36:E41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

' changed line below ***
ElseIf ((Range("D3") = "M135X" And Range("E3") = "300 hrs") Or (Range("E3") = "1500 hrs") Or (Range("E3") = "2100 hrs")) Then
ThisWorkbook.Worksheets("M100X-135X").Range("Z37:AB43").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("M100X-135X").Range("B47:D50").Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("N36:Q41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

' changed line below ***
ElseIf ((Range("D3") = "M135X" And Range("E3") = "600 hrs") Or (Range("E3") = "1200 hrs") Or (Range("E3") = "2400 hrs") Or (Range("E3") = "3000 hrs")) Then
ThisWorkbook.Worksheets("M100X-135X").Range("AL37:AN45").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("M100X-135X").Range("B47:D50").Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("V36:Y41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

' changed line below ***
ElseIf ((Range("D3") = "M135X" And Range("E3") = "900 hrs") Or (Range("E3") = "1800 hrs") Or (Range("E3") = "2700 hrs")) Then
ThisWorkbook.Worksheets("M100X-135X").Range("Z37:AB43").Copy
Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("M100X-135X").Range("B47:D50").Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Picking Lists").Range("N36:Q41").Copy
Range("Q10").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

 

[Shamos]

Thanks for the help. I tried with the change suggested, but still no luck.

 

[essentialkiwi]

How about adding the range("D3") to each as below.

This way it will always check the value of both range D3 and range E3 even when evaluating the or portion of the elseif


ElseIf ((Range("D3") = "M135X" And Range("E3") = "300 hrs") Or (Range("D3") = "M135X" And Range("E3") = "1500 hrs") Or (Range("D3") = "M135X" And Range("E3") = "2100 hrs")) Then


NOTE: if this works you will also need to update the other lines i changed in the last post

 

[Shamos]

Still no joy, thanks for the suggestion, I’ve been reading a little on “CASE”. I don’t understand it yet, but it seems to suggest that I could scrap the work I’ve already done and remove the “and or” statements.

 

[essentialkiwi]

Here's an example using Select Case statements. Hope this is helpful

just add you code for each case where it says 'do what you need to do here

Note: the msgbox es have been put in just to show that it is working correctly

Sub test()
    Dim RangeD3 As String
    Dim RangeE3 As String
    
    RangeD3 = Range("D3").Value
    RangeE3 = Range("E3").Value
    
    Select Case RangeD3
        Case "M135X"
            Select Case RangeE3
                Case "300 hrs", "1500 hrs", "2100 hrs"
                    MsgBox ("E3 = 300 hrs or 1500 hrs or 2100 hrs")
                    'do what you need to do here
                    
                Case "600 hrs", "1200 hrs", "2400 hrs", "3000 hrs"
                    MsgBox ("E3 = 600 hrs or 1200 hrs or 2400 hrs or 3000 hrs")
                    'do what you need to do here
                    
                Case "900 hrs", "1800 hrs", "2700 hrs"
                    MsgBox ("E3 = 900 hrs or 1800 hrs or 2700 hrs")
                    'do what you need to do here
                    
                Case Else
                    MsgBox ("RangeE3 not valid")
            End Select
        Case Else
            MsgBox ("RangeD3 not Equal to M135X")
    End Select
End Sub