It makes the comparison case sensitive.
Your original VBA code example does not do that, it counts instances in all rows, so please illustrate what you mean by 'on any one row' and show what you do with the result. Use the data example I posted above to provides some example before and after values.
Rlv, I've posted the rest of this macro below, but let me first explain what this entire macro does.
the macro will be run on many different excel files, these files may contain values like : Paint,CS55, R/G/R, Paint,CS55,R/B/G, Paint,CS55,B/R/R, Paint,CS55,R/R/R, Paint CS55, R/B, paint,CS55,Red...etc (the format of these values are very consistent).
Note: even know each file may contain any of the value above, but each file will only contain ONE of these values.
the CS55 is a paint material, the letter "R" is the first letter of the color red.
R/G/R is the order the paint being applied onto another product, in this case the product is painted red then gray then red paint.
I have a formula in the macro that calculates the gallonage of the red paint, based on the thickness of the paint.
So it is crucial to know how many layers of red paint is used on any given product.
Paint,CS55,R/G/R is two layers of red
Paint,CS55,R/R/R would be three layers
Paint,CS55,R/B would be one layer of the red paint
Paint,CS55,Red would be two layers
code below to calculate gallonage based on # of layers (this part of the code is working fine)
VBA Code:
If ws1.Cells(lrNew, "K").Value Like "*CS55**Red*" Then
n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lrNew), ws1.Range("K" & sr & ":K" & lrNew), "*CS55**Red*")
n2 = n1 * 0.000666 * 7.48 * 2
End If
If ws1.Cells(lrNew, "K").Value Like "*CS55*" And _
n - 1 = 1 Then
n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lrNew), ws1.Range("K" & sr & ":K" & lrNew), "*CS55**R*")
n2 = n1 * 0.000666 * 7.48
End If
If ws1.Cells(lrNew, "K").Value Like "*CS55*" And _
n - 1 = 2 Then
n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lrNew), ws1.Range("K" & sr & ":K" & lrNew), "*CS55**R*")
n2 = n1 * 0.000666 * 7.48 * 2
End If
finally the macro add one additional row to the end of the excel file to display the gallonage and few other values. (this part is working fine)
VBA Code:
lrNew = lrNew + 1
ws1.Cells(lrNew, "A") = ws1.Cells(lrNew, "A")
ws1.Cells(lrNew, "B") = "."
ws1.Cells(lrNew, "C") = n2
ws1.Cells(lrNew, "D") = "F50504"
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
full code
VBA Code:
Sub PaintCS55()
Dim ws1 As Worksheet
Dim lrNew As Long, n As Long, n1 As Long, n2 As Long, sr As Long
Set ws1 = ActiveSheet
lrNew = ws1.Range("K" & Rows.Count).End(xlUp).Row
sr = 2
If ws1.Cells(lrNew, "K").Value Like "*CS55*" Then
n = WorksheetFunction.CountIf(ws1.Range("K" & sr & ":K" & lrNew), "*R*")
End If
MsgBox n
If ws1.Cells(lrNew, "K").Value Like "*CS55**Red*" Then
n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lrNew), ws1.Range("K" & sr & ":K" & lrNew), "*CS55**Red*")
n2 = n1 * 0.000666 * 7.48 * 2
End If
If ws1.Cells(lrNew, "K").Value Like "*CS55*" And _
n - 1 = 1 Then
n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lrNew), ws1.Range("K" & sr & ":K" & lrNew), "*CS55**R*")
n2 = n1 * 0.000666 * 7.48
End If
If ws1.Cells(lrNew, "K").Value Like "*CS55*" And _
n - 1 = 2 Then
n1 = WorksheetFunction.SumIfs(ws1.Range("C" & sr & ":C" & lrNew), ws1.Range("K" & sr & ":K" & lrNew), "*CS55**R*")
n2 = n1 * 0.000666 * 7.48 * 2
End If
lrNew = lrNew + 1
ws1.Cells(lrNew, "A") = ws1.Cells(lrNew, "A")
ws1.Cells(lrNew, "B") = "."
ws1.Cells(lrNew, "C") = n2
ws1.Cells(lrNew, "D") = "F50504"
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
the result: for the example test file above, the value for n should be
2, and the value in cell C in the new row should be:
26.23, or round up to
27.
