Add an If statement to Code

[gheyman]

I need to add an If statement to this code. If the value in columns E and G are 0 then 1 else.... I want to put a 1 in column 13, for each row, if column 5 and 7 are 0. If its not then ws.Cells(a, 13) = ws.Cells(a, 12) * ws.Cells(B, 12)

Sub Add_Quantities()

'Find Parent Qty and my Qty Column M

Application.Calculation = xlManual

Dim ws As Worksheet
Dim st1, st2 As String

    Set ws = ActiveSheet

    cont = Application.WorksheetFunction.CountA(ws.Range("h:h"))

    For a = 6 To cont
    
'****************************
'Something like: If ws.Cells(a,5) = 0 and ws.Cells(a,7) = 0 Then 1 Else...  I think!
'****************************

        If ws.Cells(a, 6) = "" Then GoTo nxt
            st1 = ws.Cells(a, 6)
    For B = a - 1 To 1 Step -1
        st2 = ws.Cells(B, 8)
    If st1 = st2 Then
    ws.Cells(a, 13) = ws.Cells(a, 12) * ws.Cells(B, 12)
    
    GoTo nxt

    End If

    Next B
nxt:
    Next a
    
    
'Add formula for TA Qty down column L
    
Dim LastRow

    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    
     Range("N4:N" & LastRow).FormulaR1C1 = "=SUMIFS(C[-1],C[-10],RC[-10],C[-6],RC[-6])"
    
    
 Application.Calculation = xlAutomatic
    
End Sub

 

[Jeffrey Mahoney]

How you doin' today?

Please try this code. I changed things up a little. I am a firm believer that GOTO statements should be used as a last resort. You almost had it, all you had to do is think it out logically. EXIT FOR can get you out of the loop.

Sub Add_Quantities()


'Find Parent Qty and my Qty Column M


Application.Calculation = xlManual


Dim ws As Worksheet
Dim st1 As String, st2 As String
Dim a As Long
Dim cont As Long


  Set ws = ActiveSheet


  cont = Application.WorksheetFunction.CountA(ws.Range("h:h"))


  For a = 6 To cont
  
    If ws.Cells(a, 5) = 0 And ws.Cells(a, 7) = 0 Then
      ws.Cells(a, 13).Value -1
    End If
    
    If ws.Cells(a, 6) <> "" Then
      st1 = ws.Cells(a, 6)
      For B = a - 1 To 1 Step -1
        st2 = ws.Cells(B, 8)
        If st1 = st2 Then
          ws.Cells(a, 13) = ws.Cells(a, 12) * ws.Cells(B, 12)
          Exit For
          'GoTo nxt - No need for this
        End If
      Next B
    End If
  Next a
    
    
'Add formula for TA Qty down column L
    
Dim LastRow


    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    
    Range("N4:N" & LastRow).FormulaR1C1 = "=SUMIFS(C[-1],C[-10],RC[-10],C[-6],RC[-6])"
    
    
 Application.Calculation = xlAutomatic
    
End Sub