Method or Data member not found

zack8576

Active Member
Joined
Dec 27, 2021
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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" ?
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
 
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 ?
VBA Code:
n = Evaluate("LEN(" & cells(lrNew, 11).Address & "-LEN(SUBSTITUTE(" & cells(lrNew, 11).Address & ",""B"","""")")
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:

Rich (BB code):
  '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
 
Upvote 0
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:

Rich (BB code):
  '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
1668044987435.png


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

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top