I need to calculate the amount of CS55 in a bunch of excel files.
When column K contains CS55 and Black, we use this equation or if column K contains CS55 and two letter "B" use:
when column K contains CS55 and one letter "B", use:
full code below (add an additional row to display the amount of CS55), test file also attached
www.dropbox.com
When column K contains CS55 and Black, we use this equation or if column K contains CS55 and two letter "B" use:
VBA Code:
n = n1 * 0.000666 * 7.48 * 2
VBA Code:
n = n1 * 0.000666 * 7.48full code below (add an additional row to display the amount of CS55), test file also attached
VBA Code:
Sub PaintCS55()
Dim lrNew As Long, n1 As Long, n As Long, sr As Long
lrNew = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
sr = 2
If Cells(lrNew, "K").Value Like "*CS55**Black*" Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**Black*")
n = n1 * 0.000666 * 7.48 * 2
End If
If Cells(lrNew, "K").Value Like "*CS55*" And _
WorksheetFunction.CountIf(Range(lrNew, "K"), "*B*") = 1 Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**B*")
n = n1 * 0.000666 * 7.48
End If
If Cells(lrNew, "K").Value Like "*CS55*" And _
WorksheetFunction.CountIf(Range(lrNew, "K"), "*B*") = 2 Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**B*")
n = n1 * 0.000666 * 7.48 * 2
End If
If Cells(lrNew, "K").Value Like "*CS55*" And _
WorksheetFunction.CountIf(Range(lrNew, "K"), "*B*") = 3 Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**B*")
n = n1 * 0.000666 * 7.48 * 3
End If
lrNew = lrNew + 1
Cells(lrNew, "A") = Cells(lr, "A")
Cells(lrNew, "B") = "."
Cells(lrNew, "C") = n
Cells(lrNew, "D") = "F50504"
Cells(lrNew, "I") = "Purchased"
Cells(lrNew, "K") = "CS55 Black"
If Cells(lrNew, "C").Value Like "*0*" Then
Rows(lrNew).Delete
End If
End Sub
