Method or Data member not found

[zack8576]

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" ?

n = ws1.CountIf(Range(lrNew, "K"), "*B*")

full code below

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

 

[zack8576]

I don't understand your code. Countif was only checking if a B was in the cell in column K. If you need to calculate how many Bs are in that one cell in column K, then you would need something like your SUBSTITUTE formula, or Replace in VBA.

I was under the impression CountIf can count how many times a value appears in a cell, I guess I was way off...
B can appear a minimum of once, a maximum of 3 times in any cell that contains "CS55", I just need to count how many times B appeared in these cells.

with that being said, substitution would be the best route ? like below ?

n = Evaluate("LEN(" & cells(lrNew, 11).Address & "-LEN(SUBSTITUTE(" & cells(lrNew, 11).Address & ",""B"","""")")

 

[DanteAmor]

n = WorksheetFunction.CountIf(Range(lr, "K"), "*B*")

I removed that line from my code.


If you carefully review the lines of code that I gave you, you will see that the synaxis is different:

  'But the CountIf function is a function of WorksheetFunction:
  If ws1.Cells(lrNew, "K").Value Like "*CS55*" Then
    n = WorksheetFunction.CountIf(ws1.Range("K" & sr & ":K" & lrNew), "*B*")
  End If

 

[zack8576]

I removed that line from my code.


If you carefully review the lines of code that I gave you, you will see that the synaxis is different:

  'But the CountIf function is a function of WorksheetFunction:
  If ws1.Cells(lrNew, "K").Value Like "*CS55*" Then
    n = WorksheetFunction.CountIf(ws1.Range("K" & sr & ":K" & lrNew), "*B*")
  End If

Thanks Dante ! it is calculating the paint now. However, something still doesnt seem right. I ran it on a very simple file, only 1 row contains string with CS55 and B

qty is 1316, and there is only one B in column K. so the equation n * 0.000666 * 7.48 should be applied in this case.

However, it looks like the equation n * 0.000666 * 7.48 * 2 was applied and it spit out 13 as the answer. Very strange, I dont really understand why it is doing this...