If a cell contain a string then do THIS otherwise do THAT

[zack8576]

part of my macro is to do a tabulation of a certain item when column K contains keywords "9"" and Wrapid-Seal", counts the total qty
if K does not contain the keywords then qty = 0

every time I run this macro, qty is always zero, even know the excel file indeed have this item in K. Why is that?
Code below, test file link below, any help is appreciated !

Sub WrapidSealSmall()
    Dim lrNew As Long, i As Long
    lrNew = ActiveSheet.Range("K" & Rows.Count).End(xlUp).Row
    sr = 2
    
    For i = 2 To lastRow
        If Range("K" & i).Value Like "*9*" And _
           Range("K" & i).Value Like "*Wrapid-Seal*" Then
            n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
            Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
        Else
            n = 0
        End If
    Next i
    
    With Columns("A:K")
            lrNew = lrNew + 1
        .Rows(lrNew) = Array(Cells(lr, "A"), ".", n - 1, "F25888A", "No", """", """", """", "Purchased", """", "9"" Closure")
        If Cells(lrNew, "C").Value = 0 Or _
            Cells(lrNew, "C").Value < 0 Then
            Cells(lrNew, 4) = ","
        End If
            lrNew = lrNew + 1
        .Rows(lrNew) = Array(Cells(lr, "A"), ".", (n - 1) / 8, "F25889A", "No", """", """", """", "Purchased", """", "Primer")
        If Cells(lrNew, "C").Value = 0 Or _
           Cells(lrNew, "C").Value < 0 Then
            Rows(lrNew).Delete
        End If
            lrNew = lrNew + 1
        .Rows(lrNew) = Array(Cells(lr, "A"), ".", """", "F51040", "No", """", """", """", "Purchased", """", "9"" Wrapid-Seal")
        If n = 0 Then
            Rows(lrNew).Delete
        End If
    End With
End Sub

2022080011 SANITARY.csv

Shared with Dropbox

www.dropbox.com

 

[6StringJazzer]

every time I run this macro, qty is always zero,

There is no variable called qty. Do you mean the variable n is always 0?

    For i = 2 To lastRow
        If Range("K" & i).Value Like "*9*" And _
           Range("K" & i).Value Like "*Wrapid-Seal*" Then
            n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
            Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
        Else
            n = 0
        End If
    Next i

Your code determines the value n for each row checked. So when the For loop exits, n is the value for the lastRow, either a sum, or 0. It does not accumulate in any way. So when you check If n = 0 later in your code, it depends only on what happened for the last row.

What is n supposed to be?

 

[zack8576]

There is no variable called qty. Do you mean the variable n is always 0?

    For i = 2 To lastRow
        If Range("K" & i).Value Like "*9*" And _
           Range("K" & i).Value Like "*Wrapid-Seal*" Then
            n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
            Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
        Else
            n = 0
        End If
    Next i

Your code determines the value n for each row checked. So when the For loop exits, n is the value for the lastRow, either a sum, or 0. It does not accumulate in any way. So when you check If n = 0 later in your code, it depends only on what happened for the last row.

What is n supposed to be?

thanks 6String, you are right, there is no qty, n should have 2 outcomes:
1. n equals to the total number of Base, Riser, Cone, Top Slab (in column K) with "Sanitary" in column O. IF there is "Wrapid-Seal" anywhere in the file (column K)
2. n equals to 0 if "Wrapid-Seal" is not present in this excel file.

I attached a test file in an earlier response, that file contains Wrapid-Seal so n shouldnt be 0, but I kept getting 0

thanks !

 

[6StringJazzer]

Your code does not do what you stated in #1 and #2.

First for #1, is it supposed to be the sum of all of those values for all rows where 9 and Wrapid_Seal is found? If so, use this code instead. Also use Option Explicit and declare all your variables.

    Dim n As Long

    For i = 2 To lastRow
        If Range("K" & i).Value Like "*9*" And _
           Range("K" & i).Value Like "*Wrapid-Seal*" Then
            n = n + Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
            Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
        End If
    Next i

Else part has been removed.

 

[zack8576]

Your code does not do what you stated in #1 and #2.

First for #1, is it supposed to be the sum of all of those values for all rows where 9 and Wrapid_Seal is found? If so, use this code instead. Also use Option Explicit and declare all your variables.

    Dim n As Long

    For i = 2 To lastRow
        If Range("K" & i).Value Like "*9*" And _
           Range("K" & i).Value Like "*Wrapid-Seal*" Then
            n = n + Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
            Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*SANITARY*"))
        End If
    Next i

Else part has been removed.

tested this a few times, it works great. marked as solution
can you explain why is n = n + used here? not sure if I really understand how this works

n = [B]n + [/B]Application.Sum

 

[6StringJazzer]

Let's say the first time you find the string, the number from Sum is 10. Now you assign it to n.

The second time you find the string your Sum is 15. Your original code just assigned it to n. Now n is 15. The fact that the first number was 10 was lost.

Instead, you want n to be 10 + 15, or n = n + Sum. It keeps adding the next number to n.

You never need to set n to 0, because when you do that, you lose everything. If you don't find the string in that row, you just don't add anything to n.

 

[zack8576]

Let's say the first time you find the string, the number from Sum is 10. Now you assign it to n.

The second time you find the string your Sum is 15. Your original code just assigned it to n. Now n is 15. The fact that the first number was 10 was lost.

Instead, you want n to be 10 + 15, or n = n + Sum. It keeps adding the next number to n.

You never need to set n to 0, because when you do that, you lose everything. If you don't find the string in that row, you just don't add anything to n.

thanks 6String, thats good to know !