Loop Skip to next if value not found in row

Question

I have this loop. Basically it looks for a cell with the the name - Firm Dependent Demand and from there it offsets to a cell and it takes the value and does a calculation at the end.

Sometimes there is no cell/row with the name - Firm Dependent Demand or - Planned Dependent Demand, what happens it takes the value from the previous iteration of cell/row with the same name instead not including it. Basically what I'm trying to do is 'if not found then skip'.

I've tried adding On Error Resume Next or On Error GoTo but I can't seem to get it to work.

If someone can point me in the right direction I can finish the code.

Any help would be appreciated.

Sub Buy_US()

Dim FoundItem As Range
Dim Add As Integer
Dim VALD1 As Integer
Dim VALD2 As Integer
Dim VALD3 As Integer
Dim VALD4 As Integer
Dim VALE1 As Integer
Dim VALE2 As Integer
Dim VALE3 As Integer
Dim VALE4 As Integer
Dim VALF1 As Integer
Dim VALF2 As Integer
Dim VALF3 As Integer
Dim VALF4 As Integer
Dim ENDVALD1 As Integer
Dim ENDVALD2 As Integer
Dim ENDVALE1 As Integer
Dim ENDVALE2 As Integer
Dim ENDVALF1 As Integer
Dim ENDVALF2 As Integer
Dim PLAN1 As Integer
Dim PLAN2 As Integer
Dim PLAN3 As Integer
Dim PLAN4 As Integer
Dim PLAN5 As Integer
Dim PLAN6 As Integer
Dim ROFO1 As Integer
Dim ROFO2 As Integer
Dim X As Long

For X = 1 To Rows.count

    If Cells(X, 3).Value = "- Remaining Forecast" Then
        VALD1 = Cells(X, 3).Offset(0, 3).Value
        VALD2 = Cells(X, 3).Offset(0, 2).Value
        VALE1 = Cells(X, 3).Offset(0, 4).Value
        VALE2 = Cells(X, 3).Offset(0, 3).Value
        VALF1 = Cells(X, 3).Offset(0, 5).Value
        VALF2 = Cells(X, 3).Offset(0, 4).Value
    End If

    If Cells(X, 3).Value = "- Firm Dependent Demand" Then
        VALD3 = Cells(X, 3).Offset(0, 2).Value
        VALE3 = Cells(X, 3).Offset(0, 3).Value
        VALF3 = Cells(X, 3).Offset(0, 4).Value
    End If

    If Cells(X, 3).Value = "- Planned Dependent Demand" Then
        VALD4 = Cells(X, 3).Offset(0, 2).Value
        VALE4 = Cells(X, 3).Offset(0, 3).Value
        VALF4 = Cells(X, 3).Offset(0, 4).Value
    End If

        Add1 = VALD1 / 2 + VALD2 + VALD3 + VALD4
        Add2 = VALE1 / 2 + VALE2 + VALE3 + VALE4
        Add3 = VALF1 / 2 + VALF2 + VALF3 + VALF4

    If Cells(X, 3).Value = "CV_=CVCAL" Then
        Cells(X, 3).Offset(1, 1).Formula = Add1
        Cells(X, 3).Offset(1, 2).Formula = Add2
        Cells(X, 3).Offset(1, 3).Formula = Add3
    End If

    If Cells(X, 3).Value = "Ending Inventory" Then
        ENDVALD1 = Cells(X, 3).Offset(0, 1).Value
        ENDVALE1 = Cells(X, 3).Offset(0, 2).Value
        ENDVALF1 = Cells(X, 3).Offset(0, 3).Value
    End If

    If Cells(X, 3).Value = "CV_=CVCAL" Then
        ENDVALD2 = Cells(X, 3).Offset(1, 1).Value
        ENDVALE2 = Cells(X, 3).Offset(1, 2).Value
        ENDVALF2 = Cells(X, 3).Offset(1, 3).Value
    End If

    Subtract1 = ENDVALD1 - ENDVALD2
    Subtract2 = ENDVALE1 - ENDVALE2
    Subtract3 = ENDVALF1 - ENDVALF2

    If Cells(X, 3).Value = "CV_=CVCAL" Then
        Cells(X, 3).Offset(2, 1).Formula = Subtract1
        Cells(X, 3).Offset(2, 2).Formula = Subtract2
        Cells(X, 3).Offset(2, 3).Formula = Subtract3
    End If

    If Cells(X, 3).Value = "CV_=CVCAL" Then
        If Cells(X, 3).Offset(2, 1).Value > 0 Then
            Cells(X, 3).Offset(2, 1).Font.Color = RGB(0, 176, 80)
    End If
    If Cells(X, 3).Offset(2, 1).Value < 0 Then
            Cells(X, 3).Offset(2, 1).Font.Color = RGB(255, 0, 0)
        End If
    End If

    If Cells(X, 3).Value = "CV_=CVCAL" Then
        If Cells(X, 3).Offset(2, 2).Value > 0 Then
            Cells(X, 3).Offset(2, 2).Font.Color = RGB(0, 176, 80)
    End If
    If Cells(X, 3).Offset(2, 2).Value < 0 Then
            Cells(X, 3).Offset(2, 2).Font.Color = RGB(255, 0, 0)
        End If
    End If

    If Cells(X, 3).Value = "CV_=CVCAL" Then
        If Cells(X, 3).Offset(2, 3).Value > 0 Then
        Cells(X, 3).Offset(2, 3).Font.Color = RGB(0, 176, 80)
    End If
    If Cells(X, 3).Offset(2, 3).Value < 0 Then
        Cells(X, 3).Offset(2, 3).Font.Color = RGB(255, 0, 0)
        End If
    End If

    If Cells(X, 3) = "CV_=CVCAL" Then
        Cells(X, 3).Offset(1, 4) = "BUY"
        Cells(X, 3).Offset(1, 5) = "MOQ"
        Cells(X, 3).Offset(2, 5) = "54"
        Cells(X, 3).Offset(2, 6) = "UOM/PAL"
        Cells(X, 3).Offset(2, 4).Interior.Color = RGB(0, 176, 240)
        Cells(X, 3).Offset(2, 4).Font.ThemeColor = xlThemeColorDark1
        Cells(X, 3).Offset(2, 5).Interior.Color = RGB(0, 112, 192)
        Cells(X, 3).Offset(2, 5).Font.ThemeColor = xlThemeColorDark1
        Cells(X, 3).Offset(1, 8).Interior.Color = RGB(255, 204, 153)
        Cells(X, 3).Offset(2, 4).Select
        ActiveCell.FormulaR1C1 = "=IFERROR(ROUNDUP(((RC[-1]/1)/RC[1]),0)*RC[1],0)"
        Cells(X, 3).Offset(1, 7).Select
        ActiveCell.FormulaR1C1 = "=RC[2]-RC[1]"
    End If

    If Cells(X, 3) = "+ Planned Orders (Supply)" Then
        PLAN1 = Cells(X, 3).Offset(0, 4).Value
        PLAN2 = Cells(X, 3).Offset(0, 5).Value
        PLAN3 = Cells(X, 3).Offset(0, 6).Value
        PLAN4 = Cells(X, 3).Offset(0, 7).Value
        PLAN5 = Cells(X, 3).Offset(0, 8).Value
        PLAN6 = Cells(X, 3).Offset(0, 9).Value
    End If

    Add = PLAN1 + PLAN2 + PLAN3 + PLAN4 + PLAN5 + PLAN6

    If Cells(X, 3) = "CV_=CVCAL" Then
        Cells(X, 3).Offset(1, 8).Formula = Add
    End If

Next

End Sub

Does this https://stackoverflow.com/questions/8680640/vba-how-to-conditionally-skip-a-for-loop-iteration answer your question? — rgvlee, Jun 14 '20 at 04:53

Loop Skip to next if value not found in row

0 Answers0