Add an If statement to Code

gheyman

Well-known Member
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``````

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
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``````

Replies
1
Views
21
Replies
2
Views
199
Replies
6
Views
83
Replies
6
Views
81
Replies
0
Views
35