Equation returns 0 when it should not (VBA)

[zack8576]

I have a subroutine that counts the total square foot of a paint (CS-55) in any given excel file, converts it to gallonage, and add a row to the end of the file to display this gallonage number.

below is a screenshot of a test file, the square footage is in column C, and description is in K.
If description contains one letter 'B" (row 11), it calculates the gallonage based on one layer of paint, otherwise it always calculates based on two layers.

the code has been returning the number 0 on a bunch of test files, and I am really not sure why. any help is greatly appreciated as always.
test file link below, full code below as well, thanks !

2022100191 conwrap paint multi color.csv

Shared with Dropbox

www.dropbox.com

Sub PaintCS55Black()
  Dim ws1 As Worksheet
  Dim lrNew As Long, lr As Long, n As Long, n1 As Long, n2 As Long, sr As Long, RCount As Long, desc As String
  Set ws1 = ActiveSheet

   lr = ws1.Range("K" & Rows.Count).End(xlUp).Row
   sr = 2

   If InStr(desc, "CS55") Then
        n = Len(desc) - (Len(Replace(desc, "B", "", 1, , vbBinaryCompare)))
   End If
   RCount = n

   If ws1.Cells(lr, "K").Value Like "*CS55**Black*" Then
         n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lr), ws1.Range("K" & sr & ":K" & lr), "*CS55**Black*")
         n2 = n1 * 0.000666 * 7.48 * 2
   End If
   If ws1.Cells(lr, "K").Value Like "*CS55*" And _
      n - 1 = 0 Then
         n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lr), ws1.Range("K" & sr & ":K" & lr), "*CS55**Black*")
         n2 = n1 * 0.000666 * 7.48 * 2
   End If
   If ws1.Cells(lr, "K").Value Like "*CS55*" And _
      n - 1 = 1 Then
         n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lr), ws1.Range("K" & sr & ":K" & lr), "*CS55**B*")
         n2 = n1 * 0.000666 * 7.48
   End If
   If ws1.Cells(lr, "K").Value Like "*CS55*" And _
      n - 1 = 2 Then
         n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lr), ws1.Range("K" & sr & ":K" & lr), "*CS55**B*")
         n2 = n1 * 0.000666 * 7.48 * 2
   End If
   lrNew = lr + 1
   ws1.Cells(lrNew, "A") = ws1.Cells(lr, "A")
   ws1.Cells(lrNew, "B") = "."
   ws1.Cells(lrNew, "C") = n2
   ws1.Cells(lrNew, "D") = "F62655"
   ws1.Cells(lrNew, "I") = "Purchased"
   ws1.Cells(lrNew, "K") = "CS55 Black"
   If ws1.Cells(lrNew, "C").Value Like "*0*" Then
    Rows(lrNew).Delete
   End If
End Sub

 

[zack8576]

ok we crossed over in the ether, you can ignore my previous post it was before you added the correction. All good. Goodnight from Australia

Thanks Alex, good night

 

[6StringJazzer]

Yikes. You guys have been busy since I've been gone. Good luck. (BTW the requirement for 3x for B/B/B was not clear in the description, sorry I missed that).

 

[zack8576]

Yikes. You guys have been busy since I've been gone. Good luck. (BTW the requirement for 3x for B/B/B was not clear in the description, sorry I missed that).

thanks for all your help Jeff !