Excel-VBA if statement runs without checking and validating the right values

Question

I have little snippet of code which is supposed to extract the value of a few cells if their headers correspond to certain categories. The variables are private. However, the code runs and selects the first if statement and sets it to true EVEN IF the header is not "HFO 360" or "HFO 180" or "HFO 60".

Option Explicit
Option Base 1

Sub_Consumption()    
Dim HFOConsumed, HFORemaining, MDOConsumed, MDORemaining As Double

With Activesheet

HFOConsumed = 0
HFORemaining = 0
MDOConsumed = 0
MDORemaining = 0

If .Cells("M44") = "HFO 380" Or .Cells("M44") = "HFO 180" Or .Cells("M44") = "HFO 60" Then
    HFOConsumed = .Range("M45").Value
    HFORemaining = .Range("M46").Value
ElseIf .Cells("N44") = "HFO 380" Or .Cells("N44") = "HFO 180" Or .Cells("N44") = "HFO 60" Then
    HFOConsumed = .Range("N45").Value
    HFORemaining = .Range("N46").Value
ElseIf .Cells("O44") = "HFO 380" Or .Cells("O44") = "HFO 180" Or .Cells("O44") = "HFO 60" Then
    HFOConsumed = .Range("O45").Value
    HFORemaining = .Range("O46").Value
Else
    HFOConsumed = 0
    HFORemaining = 0
End If

.Range("P45") = HFOConsumed
.Range("P46") = HFORemaining


If .Cells("M44") = "MDO" Or .Cells("M44") = "MDO M/E" Then
    MDOConsumed = .Range("M45").Value
    MDORemaining = .Range("M46").Value
ElseIf .Cells("M44") = "MDO" Or .Cells("M44") = "MDO M/E" Then
    MDOConsumed = .Range("N45").Value
    MDORemaining = .Range("N46").Value
ElseIf .Cells("M44") = "MDO" Or .Cells("M44") = "MDO M/E" Then
    MDOConsumed = .Range("O45").Value
    MDORemaining = .Range("O46").Value
Else
    MDOConsumed = 0
    MDORemaining = 0
End If

.Range("Q45") = MDOConsumed
.Range("Q46") = MDORemaining

End With
End Sub

Answer 1

Your syntax is wrong, I'm afraid. This:

If .Range("M44") And .Range("N44") And .Range("O44") = "MDO" Or "MDO M/E" Then

has to be broken out specifically - something like:

If (.Range("M44").Value = "MDO" Or .Range("M44").Value = "MDO M/E") And (.Range("N44").Value = "MDO" Or .Range("N44").Value = "MDO M/E") And (.Range("O44").Value = "MDO" Or .Range("O44").Value = "MDO M/E") Then

depending on whether all three cells have to contain the same one of those two values, or they just have to all contain one of the two values.

Also, note that it's WorksheetFunction.Sum and not Worksheet.Function.Sum so your .Function.Sum won't work.