in this macro, I am using the CountIf feature to count the # of letter "B" in column K when "CS55" is in column K
ws1 is defined, why the error message ""method or data member not found" ?
full code below
ws1 is defined, why the error message ""method or data member not found" ?
VBA Code:
n = ws1.CountIf(Range(lrNew, "K"), "*B*")
full code below
VBA Code:
Sub PaintCS55()
Dim ws1 As Worksheet, lrNew As Long, n As Long, n1 As Long, n2 As Long
lrNew = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
sr = 2
If Cells(lrNew, "K").Value Like "*CS55*" Then
n = ws1.CountIf(Range(lrNew, "K"), "*B*")
End If
If Cells(lrNew, "K").Value Like "*CS55**Black*" Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**Black*")
n2 = n1 * 0.000666 * 7.48 * 2
End If
If Cells(lrNew, "K").Value Like "*CS55*" And _
n = 1 Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**B*")
n2 = n1 * 0.000666 * 7.48
End If
If Cells(lrNew, "K").Value Like "*CS55*" And _
n = 2 Then
n1 = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*CS55**B*")
n2 = n1 * 0.000666 * 7.48 * 2
End If
lrNew = lrNew + 1
Cells(lrNew, "A") = Cells(lr, "A")
Cells(lrNew, "B") = "."
Cells(lrNew, "C") = n2
Cells(lrNew, "D") = "F50504"
Cells(lrNew, "I") = "Purchased"
Cells(lrNew, "K") = "RFS Gasket"
If Cells(lrNew, "C").Value Like "*0*" Then
Rows(lrNew).Delete
End If
End Sub