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

 

[6StringJazzer]

the code has been returning the number 0

This is not a function so it doesn't return anything. I assume you are talking about the total gallonage. Your code uses cryptic variables names so I'm not sure what this is in your code. What value are you interested in here?

Your first line of code looks at variable desc for "CS55" but the variable hasn't been set yet so this will always be 0 and the If will always be FALSE.

All of your If statements evaluate as FALSE. So n2 is never set, it will be 0, and the new row will be deleted as soon as it's created.

Your last three If conditions have one common conditions then a second condition that is mutually exclusive so you should have one outer If for

   If ws1.Cells(lr, "K").Value Like "*CS55*"

and three inner Ifs coded as If/ElseIf/ElseIf or a Select Case on n - 1.

The code looks only at the last row. Your description makes it sound like maybe you want to look at every row.

Please give a more complete description of what you want this code to do.

__________________
Here is a coding issue unrelated to what you asked:

Don't do this

   If ws1.Cells(lrNew, "C").Value Like "*0*" Then

unless you are looking for a 0 anywhere in the cell. If you are just testing for this cell being 0, you should check n2 before you even insert the new row. There is no point in inserted it and then immediately deleting it.

   lrNew = lr + 1
   If n2 <> 0 Then
      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"
   End If

 

[zack8576]

This is not a function so it doesn't return anything. I assume you are talking about the total gallonage. Your code uses cryptic variables names so I'm not sure what this is in your code. What value are you interested in here?

Your first line of code looks at variable desc for "CS55" but the variable hasn't been set yet so this will always be 0 and the If will always be FALSE.

All of your If statements evaluate as FALSE. So n2 is never set, it will be 0, and the new row will be deleted as soon as it's created.

Your last three If conditions have one common conditions then a second condition that is mutually exclusive so you should have one outer If for

   If ws1.Cells(lr, "K").Value Like "*CS55*"

and three inner Ifs coded as If/ElseIf/ElseIf or a Select Case on n - 1.

The code looks only at the last row. Your description makes it sound like maybe you want to look at every row.

Please give a more complete description of what you want this code to do.

__________________
Here is a coding issue unrelated to what you asked:

Don't do this

   If ws1.Cells(lrNew, "C").Value Like "*0*" Then

unless you are looking for a 0 anywhere in the cell. If you are just testing for this cell being 0, you should check n2 before you even insert the new row. There is no point in inserted it and then immediately deleting it.

   lrNew = lr + 1
   If n2 <> 0 Then
      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"
   End If

Thanks Jeff for the response.

I have many excel files that contain this paint (CS-55 Black), and many other files that do not.
For the files that do contain this material, I want to calculate the total gallonage of the paint (n2) and display this number in column C on the new row after the last row;
for the files that do not contain this material, n2 will be 0 and the new row will be removed.

the description in column K can be any of these: CS-55 B/G/R, CS-55 B/B/R, CS-55 Black, CS-55 B/G, CS-55 R/G/B...etc
If there are 2 letter B in the description, or if Black is in description, it will get 2 layers.
otherwise, it gets 1 layer.

the qty of the material in these excel files are sq ft, they are always in column C. They typically exist on multiple rows, n1 is the total sq ft for all the rows that contain this paint.
Then I am using the equation n2 = n1 * 0.000666 * 7.48 to convert n1 to gallon. If there are 2 layers of the paint then the equation becomes n2 = n1 * 0.000666 * 7.48 * 2
the value of n2 will then be displayed in column C on row lrNew

for the example below, n1 = 213.87 sqft, and description contains 1 letter B, so it is 1 layer.
So we have: n2 = 213.87 * 0.000666 * 7.48 = 1.06 ( I would prefer to always round up n2 to the next integer , so n2 in this case should be 2)

 

[6StringJazzer]

OK so your code needs to look at every line of data. Your code looks only at the last line. I will update your code but it will be a few hours before I can sit down to do it.

 

[zack8576]

OK so your code needs to look at every line of data. Your code looks only at the last line. I will update your code but it will be a few hours before I can sit down to do it.

thanks jeff, I really appreciate it

 

[6StringJazzer]

I can't figure out what you are trying to do here

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

desc is never assigned a value, and it is never used again after this line of code.

 

[zack8576]

I can't figure out what you are trying to do here

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

desc is never assigned a value, and it is never used again after this line of code.

Jeff, desc was short for description, which are in column K.
As you mentioned in your previous post, this value is not defined properly.
I am still new to VBA, but it looks like this If statement is obsolete and serves no real purpose here...

 

[6StringJazzer]

Also note that column C has strings, not numbers. They will all be treated as 0. You have to strip off the " SqFt" ending to get a number.

 

[zack8576]

Also note that column C has strings, not numbers. They will all be treated as 0. You have to strip off the " SqFt" ending to get a number.

 With Range("C", Cells(Rows.Count, "C").End(3))
        .Replace What:="", Replacement:=vbNullString, LookAt:=xlPart
        .Replace What:="sqft", Replacement:=vbNullString, LookAt:=xlPart
    End With

Something similar to this would remove those characters right ?

 

[6StringJazzer]

Yes, if you want to change your source data.