# Equation returns 0 when it should not (VBA)



## zack8576 (Saturday at 8:56 PM)

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 (Sunday at 11:51 AM)

> 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 (Sunday at 1:00 PM)

6StringJazzer said:


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


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 (Sunday at 3:02 PM)

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 (Sunday at 3:26 PM)

6StringJazzer said:


> 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 (Monday at 10:29 AM)

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 (Monday at 10:36 AM)

6StringJazzer said:


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


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 (Monday at 10:45 AM)

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 (Monday at 11:28 AM)

6StringJazzer said:


> 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 (Monday at 11:57 AM)

Yes, if you want to change your source data.


----------



## zack8576 (Saturday at 8:56 PM)

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 (Monday at 12:00 PM)

6StringJazzer said:


> Yes, if you want to change your source data.


changing source data in column C should be fine

thanks !


----------



## zack8576 (Monday at 12:01 PM)

6StringJazzer said:


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


Jeff, I am still not sure how to fix this issue though


----------



## 6StringJazzer (Monday at 12:09 PM)

Based on your description I can't tell if this is supposed to be counted. There is no B at all.


Paint,CS55,R/G


----------



## 6StringJazzer (Monday at 12:14 PM)

Here is the revised code. You will need to do a calculation by hand to make sure this matches your requirements. It wasn't clear if you wanted to count rows for CS55 that have no B in the code.

```
Option Explicit

Sub PaintCS55Black()

  Dim ws1 As Worksheet
  Dim LastRow As Long, TotalGallons As Double, Desc As String
  Set ws1 = ActiveSheet
  Dim Row As Long
  Dim SqFt As Double
  Const ConvertToSqFt As Double = 0.000666 * 7.48
  
  
   LastRow = ws1.Range("K" & Rows.Count).End(xlUp).Row

   For Row = 2 To LastRow
   
      Desc = ws1.Cells(Row, "K").Value
      If Desc Like "*CS55*" Then
      
         SqFt = ConvertToSqFt * CDbl(Replace(ws1.Cells(Row, "C"), " SqFt", ""))
         
         TotalGallons = TotalGallons + SqFt
         
         If Desc Like "*B*B*" Or Desc Like "*Black*" Then
            TotalGallons = TotalGallons + SqFt
         End If
         
      End If
      
   Next Row
         
   
   If TotalGallons = 0 Then
      MsgBox "No matches found."
   Else
   
      LastRow = LastRow + 1
      With ws1
         Cells(LastRow, "A") = Cells(LastRow, "A")
         Cells(LastRow, "B") = "."
         Cells(LastRow, "C") = TotalGallons
         Cells(LastRow, "D") = "F62655"
         Cells(LastRow, "I") = "Purchased"
         Cells(LastRow, "K") = "CS55 Black"
      End With
   End If
   
End Sub
```


----------



## zack8576 (Monday at 12:34 PM)

6StringJazzer said:


> Based on your description I can't tell if this is supposed to be counted. There is no B at all.
> 
> 
> Paint,CS55,R/G


There are files that will have description like CS-55, R/G, or something similar, without the letter B
for these files, n2 should return as 0, and then the row will be deleted


----------



## zack8576 (Monday at 12:35 PM)

6StringJazzer said:


> Here is the revised code. You will need to do a calculation by hand to make sure this matches your requirements. It wasn't clear if you wanted to count rows for CS55 that have no B in the code.
> 
> ```
> Option Explicit
> ...


thanks Jeff

I will run it a few times on separate files, and do some hand calc to see if the numbers match
I will let you know shortly thanks !!


----------



## zack8576 (Monday at 1:33 PM)

6StringJazzer said:


> Here is the revised code. You will need to do a calculation by hand to make sure this matches your requirements. It wasn't clear if you wanted to count rows for CS55 that have no B in the code.
> 
> ```
> Option Explicit
> ...


here are the results, some numbers are slightly different due to rounding

Test file 1:
hand calc: *9.77* gallon, VBA: *9.78*




Test file 2:
hand calc: *26.23*, VBA: *26.23*




Test file 3, no B or Black:
hand calc:* 0*, VBA: *26.23*




Any file that does not contain B or Black are not calculated correctly


----------



## zack8576 (Monday at 2:38 PM)

6StringJazzer said:


> Here is the revised code. You will need to do a calculation by hand to make sure this matches your requirements. It wasn't clear if you wanted to count rows for CS55 that have no B in the code.
> 
> ```
> Option Explicit
> ...


I tried to add a statement like 

```
If Desc Like "*CS55*" And _
   Not Desc Like "*Black*" Or _
   Not Desc Like "*B*" Then
         SqFt = ConvertToSqFt * CDbl(Replace(ws1.Cells(Row, "C"), " SqFt", ""))
         TotalGallons = 0
```

this did fix the issue on any file without B or Black, but the TotalGallons on files with letter B or Black are spitting out the number 0 now


----------



## Alex Blakenburg (Monday at 7:23 PM)

Hi Zack, I think at some point you need to address your end to end process. I suspect that Power Query would be a better fit for what you are trying to do.
What do you want to happen when you have *Paint,CS55,B/B/B *? do you want 3x ?
How do you actually know that the B's are all Black and none of them are Blue ?


----------



## zack8576 (Monday at 7:44 PM)

Alex Blakenburg said:


> Hi Zack, I think at some point you need to address your end to end process. I suspect that Power Query would be a better fit for what you are trying to do.
> What do you want to happen when you have *Paint,CS55,B/B/B *? do you want 3x ?
> How do you actually know that the B's are all Black and none of them are Blue ?


Thanks Alex. I did explain the process near the top of this post, I've posted it below
 but I did fail to mention special cases like B/B/B
if there are 3 B, indeed I will need to 3x, if it is Paint,CS55,R/G, it will be 0.
luckily for me, there are 3 possible colors, Black(B), Gray(G), and Red(R), so B will always be Black



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



Jeff's code works almost perfectly, it only fails when there is no letter *B* or *Black* after *Paint,CS55*

And as a lowly amateur, I've been trying to fix this code and have been failing


----------



## zack8576 (Saturday at 8:56 PM)

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


----------



## Alex Blakenburg (Monday at 7:56 PM)

But you didn't answer my question.
If you want B/B/B to be x3, the code is not doing that - need this confirmed.

If you want to exclude R/G it is not doing that.
If you have just Black it is counting it twice.


----------



## Alex Blakenburg (Monday at 8:09 PM)

This outputs the conversion information starting at Column V so you can validate the results.
You can delete or comment out the 3 sections using an array. If you only comment out the 3rd section you can still see the array values in the watch window.
It also uses your original line of code counting the number of Bs in the descrtiption.


```
' Based on Jeff's Post 14
Sub PaintCS55Black_withValidationArray()

  Dim ws1 As Worksheet
  Dim LastRow As Long, TotalGallons As Double, Desc As String
  Set ws1 = ActiveSheet
  Dim Row As Long
  Dim SqFt As Double, Gallons As Double
  Dim CountBs As Long
  
  Const ConvertToSqFt As Double = 0.000666 * 7.48
  
   LastRow = ws1.Range("K" & Rows.Count).End(xlUp).Row
   
   ' *** This section can be deleted - validation only (1 of 3)
   Dim arr() As Variant
   Dim ColArrOut As String
   ReDim arr(1 To LastRow, 1 To 6) As Variant
   ColArrOut = "V"                      ' <--- Output of validation Columns
   ' *** end of section

   For Row = 2 To LastRow
   
      Desc = ws1.Cells(Row, "K").Value
      If Desc Like "*CS55*" Then
        CountBs = Len(Desc) - (Len(Replace(Desc, "B", "", 1, , vbBinaryCompare)))

         SqFt = CountBs * CDbl(Replace(ws1.Cells(Row, "C"), " SqFt", ""))
         Gallons = ConvertToSqFt * SqFt
         TotalGallons = TotalGallons + Gallons
         
   ' *** This section can be deleted - validation only (2 of 3)
         arr(Row, 1) = Row
         arr(Row, 2) = Desc
         arr(Row, 3) = SqFt
         arr(Row, 4) = CountBs
         arr(Row, 5) = Gallons
         arr(Row, 6) = TotalGallons
        ' *** end of section
      End If
      
   Next Row
         
   
   If TotalGallons = 0 Then
      MsgBox "No matches found."
   Else
   
      LastRow = LastRow + 1
      With ws1
         Cells(LastRow, "A") = Cells(LastRow, "A")
         Cells(LastRow, "B") = "."
         Cells(LastRow, "C") = TotalGallons
         Cells(LastRow, "D") = "F62655"
         Cells(LastRow, "I") = "Purchased"
         Cells(LastRow, "K") = "CS55 Black"
      End With
   End If
   
   ' *** This section can be deleted - validation only (3 of 3)
   Range(ColArrOut & "1").Resize(UBound(arr), UBound(arr, 2)) = arr
   Range(ColArrOut & "1").Resize(, UBound(arr, 2)) = Array("Row No", "Desc", "Sq Ft", "Cnt Bs", "Gallons", "Total Gallons")
   ' *** end of section
   
End Sub
```


----------



## zack8576 (Monday at 9:16 PM)

Alex Blakenburg said:


> But you didn't answer my question.
> If you want B/B/B to be x3, the code is not doing that - need this confirmed.


Yes, if it is B/B/B, it needs to  3x


----------



## zack8576 (Monday at 9:17 PM)

Alex Blakenburg said:


> If you want to exclude R/G it is not doing that.
> If you have just Black it is counting it twice.


If it is R/G, or R/R, or R/R/R, or G/R...etc, anything without letter B or Black, it will need to be 0


----------



## Alex Blakenburg (Monday at 9:18 PM)

Thanks I have assumed that in the code I  posted on #22. Try it and let me know how go.


----------



## zack8576 (Monday at 9:45 PM)

Alex Blakenburg said:


> Thanks I have assumed that in the code I  posted on #22. Try it and let me know how go.


I've tested it on 3 files, so far the outcomes are correct.
There is one thing I dont really understand though, I noticed you and Jeff like to use this to add a new row:


```
LastRow = LastRow + 1
```

However, this always resulted in column A in the new row does not copy the value from cell above






In Jeff's code I changed this to 

```
lrNew = LastRow + 1
```
and it was copying the value from the cell above just fine

But when I did the same thing to your code, instead of adding a new row, now the data that was supposed to go on the new row are on row 10, which is the last row on the original file.
Any pointer would be greatly appreciated


----------



## zack8576 (Monday at 9:47 PM)

Alex Blakenburg said:


> If TotalGallons = 0 Then
> MsgBox "No matches found."
> Else
> 
> ...


Alex I really like the way you handled this part of the code, if value is zero then no row added
I used to always add a row first, if value in C is 0 then remove the row

I will use your method on some of the future codes


----------



## Alex Blakenburg (Monday at 9:58 PM)

Jeff gets the credit for that one. It's in his original code.


----------



## zack8576 (Monday at 10:07 PM)

Alex Blakenburg said:


> Jeff gets the credit for that one. It's in his original code.


You are right, Jeff did write that originally. It is very late and I must be sleepy Lol...


----------



## zack8576 (Monday at 10:09 PM)

Alex Blakenburg said:


> Jeff gets the credit for that one. It's in his original code.


 

```
If TotalGallons = 0 Then
      MsgBox "No matches found."
   Else
      lrNew = LastRow + 1
      With ws1
         Cells(lrNew, "A") = Cells(LastRow, "A")
         Cells(lrNew, "B") = "."
         Cells(lrNew, "C") = TotalGallons
         Cells(lrNew, "D") = "F62655"
         Cells(lrNew, "I") = "Purchased"
         Cells(lrNew, "K") = "CS55 Black"
      End With
   End If
```

I've added a lrNew to the code, and slightly modified this part
now it is copying the value in column A, and it seems everything else is working the way it's supposed to be


----------



## zack8576 (Saturday at 8:56 PM)

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


----------



## bebo021999 (Monday at 11:32 PM)

One way, using SUMPRODUCT function with range once, no loop:

```
Option Explicit
Sub PaintCS55Black()
Dim lr&, SQ As String, CS As String, Gal As Double
lr = Cells(Rows.Count, "A").End(xlUp).Row
SQ = Range("C2:C" & lr).Address: CS = Range("K2:K" & lr).Address
Gal = Evaluate("=SUMPRODUCT(SUBSTITUTE(" & SQ & ",""SqFt"","""")*ISNUMBER(SEARCH(""CS55""," & CS & "))*(LEN(" & CS & ")-LEN(SUBSTITUTE(" & CS & ",""B"",""""))))*0.000666*7.48")
Range(Cells(lr + 1, "A"), Cells(lr + 1, "K")).Value = Array(Cells(lr, "A").Value, ".", Gal, "F62655", , , , , "Purchase", , "CS55 Black")
End Sub
```


----------



## zack8576 (Tuesday at 3:56 AM)

bebo021999 said:


> One way, using SUMPRODUCT function with range once, no loop:
> 
> ```
> Option Explicit
> ...


Thanks Bebo! I will try this out as well


----------



## zack8576 (Tuesday at 4:01 AM)

Alex Blakenburg said:


> This outputs the conversion information starting at Column V so you can validate the results.
> You can delete or comment out the 3 sections using an array. If you only comment out the 3rd section you can still see the array values in the watch window.
> It also uses your original line of code counting the number of Bs in the descrtiption.
> 
> ...


Hey Alex
CS55,Black should result in 2x, it is only 1x right now
and I have another question, is there a way to have the result to be an integer ?

thanks !


----------



## bebo021999 (Tuesday at 4:48 AM)

Just update: Black * 2 and Integer outcome


```
Option Explicit
Sub PaintCS55Black()
Dim lr&, SQ As String, CS As String, Gal As Double
lr = Cells(Rows.Count, "A").End(xlUp).Row
SQ = Range("C2:C" & lr).Address: CS = Range("K2:K" & lr).Address
Gal = Evaluate("=INT(SUMPRODUCT(SUBSTITUTE(" & SQ & ",""SqFt"","""")*ISNUMBER(SEARCH(""CS55""," & CS & "))*(LEN(SUBSTITUTE(" & CS & ", ""Black"", ""B/B""))-LEN(SUBSTITUTE(SUBSTITUTE(" & CS & ", ""Black"", ""B/B""),""B"",""""))))*0.000666*7.48)")
Range(Cells(lr + 1, "A"), Cells(lr + 1, "K")).Value = Array(Cells(lr, "A").Value, ".", Gal, "F62655", , , , , "Purchase", , "CS55 Black")
End Sub
```


----------



## Alex Blakenburg (Tuesday at 5:28 AM)

zack8576 said:


> Hey Alex
> CS55,Black should result in 2x, it is only 1x right now
> and I have another question, is there a way to have the result to be an integer ?


Not sure what the logic is for making Black time 2 but just replace this:

```
CountBs = Len(Desc) - (Len(Replace(Desc, "B", "", 1, , vbBinaryCompare)))
```

With this:

```
If Desc Like "*Black*" Then
            CountBs = 2
        Else
            CountBs = Len(Desc) - (Len(Replace(Desc, "B", "", 1, , vbBinaryCompare)))
        End If
```

At what point do you want it converted to an integer. If it is only the final total then just add it to the output line as per the below:

```
Cells(LastRow, "C") = Int(TotalGallons)
```


----------



## zack8576 (Tuesday at 7:54 AM)

bebo021999 said:


> Just update: Black * 2 and Integer outcome
> 
> 
> ```
> ...


Thanks Bebo !


----------



## zack8576 (Tuesday at 8:14 AM)

Alex Blakenburg said:


> Not sure what the logic is for making Black time 2 but just replace this:
> 
> ```
> CountBs = Len(Desc) - (Len(Replace(Desc, "B", "", 1, , vbBinaryCompare)))
> ...


Alex, thank you, these are the 2 answers I am looking for.

Not trying to bore you to death, so I will keep it short

We have an outdated system, that goes like this:
software 1 spits out a csv file ----- VBA codes to process it ----- then this processed csv files gets imported into a database

in software 1 database, the description of 1 layer of black paint, is CS55 Black, and we have been using this same format for decades
Sure I can modify it to CS55 B in the database so it will show CS55 B in the csv , but this would cause mass confusion amoung the many dozens of users of software 1. (Sounds silly, but it is true)

Hopefully that explains the reasoning for using CS55 Black and not CS55 B


----------



## zack8576 (Tuesday at 8:17 AM)

Alex Blakenburg said:


> Not sure what the logic is for making Black time 2 but just replace this:
> 
> ```
> CountBs = Len(Desc) - (Len(Replace(Desc, "B", "", 1, , vbBinaryCompare)))
> ...


correction: in software 1 database, the description of 2 layer of just black paint, is CS55 Black


----------



## Alex Blakenburg (Tuesday at 8:17 AM)

zack8576 said:


> Alex, thank you, these are the 2 answers I am looking for.
> 
> Not trying to bore you to death, so I will keep it short
> 
> ...


I am about to log off but my issue wasn't whether you called it Black or B. It was that if you just called it B the logic said it was to be x 1, but when you call it Black you have specified it should be x 2.


----------



## Alex Blakenburg (Tuesday at 8:18 AM)

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


----------



## zack8576 (Saturday at 8:56 PM)

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 (Tuesday at 8:20 AM)

Alex Blakenburg said:


> 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 (Tuesday at 10:40 AM)

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 (Tuesday at 11:06 AM)

6StringJazzer said:


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


----------

