CODEWITHSUNDEEP
excelvba
SimDimJack
SimDimJack

Reputation: 1

How to fix 'End if without block if' if there is only 1 if and 1 end if?

If I have an if statement between to For Each statements will this effect where I need to place my 'end if' and how many end if's statements I am using?

I have tried moving around the end if and adding more but none help. 

Sheets("Arrivals").Select

Dim cel As Range
Dim unit As Range

ParcelCount = Range("BW8").Value
LowerParcelCount = Range("BW5").Value
ThresholdCount = 0

For Each cel In Range("BQ3:BQ78")
    If cel.Value > LowerParcelCount Then
        For Each unit In Range("C3:C78")
            ThresholdCount = ThresholdCount + unit.Value
    End If
Next cel
Next unit

Range("BS16") = ThresholdCount

I expect the code to run. If the cell value in range BQ3:BQ78 meets the criteria the call value in range C3:C78 should be summed to the empty variable ThresholdCount.

Upvotes: 0

Views: 186

Answers (1)

Damian
Damian

Reputation: 5174

You missed the next unit line:

Option Explicit
Sub Test()

    Sheets("Arrivals").Select

    Dim cel As Range
    Dim unit As Range

    ParcelCount = Range("BW8").Value
    LowerParcelCount = Range("BW5").Value
    ThresholdCount = 0

    For Each cel In Range("BQ3:BQ78")
        If cel.Value > LowerParcelCount Then
            For Each unit In Range("C3:C78")
                ThresholdCount = ThresholdCount + unit.Value
            Next unit
        End If
    Next cel

    Range("BS16") = ThresholdCount


End Sub

Also you should learn to indent your code, Avoid to use .Select and declare all your variables using Option Explicit

Here, your code like it should be:

Option Explicit
Sub Test()

    Dim ws As Worksheet 'declare your worksheets

    Set ws = ThisWorkbook.Sheets("Arrivals") 'like this you will refer to it using ws

    Dim cel As Range, unit As Range
    Dim ParcelCount As Long, LowerParcelCount As Long, ThresholdCount As Long

    With ws 'you could also use With ThisWorkbook.Sheets("Arrivals") if you are not using that sheet anymore
        ParcelCount = .Range("BW8").Value
        LowerParcelCount = .Range("BW5").Value
        ThresholdCount = 0

        For Each cel In .Range("BQ3:BQ78")
            If cel.Value > LowerParcelCount Then
                For Each unit In .Range("C3:C78")
                    ThresholdCount = ThresholdCount + unit.Value
                Next unit
            End If
        Next cel
        .Range("BS16") = ThresholdCount
    End With

End Sub

As you can see, there is no need to select the sheet arrivals, since we declared it on the top and use it's reference all along because everything is inside the With ws

Upvotes: 5

Related Questions