Add an If statement to Code

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
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)


Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,766
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,991
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top