excel - Using a wildcard with a cell reference. What am I missing? -

in sheet list of names. attempting count different names on sheet in table on sheet b, column b. names not exact , referring cell , not writing in name. sub below works think not using wildcqard correctly. please if can. in advance.

sub countif_crr_cnt_until_lastrow()      dim lastrow long     dim wb1 workbook       set wb1 = workbooks("macro client v.01.xlsm")      lastrow = wb1.sheets("a").range("a:a").find("overall - total", searchorder:=xlbyrows, searchdirection:=xlprevious).row      = 21 lastrow  cells(i, 10) = application.countifs(wb1.sheets("b").range("b:b"), "*" & cells(i, 3) & "*")  next  end sub 

i believe issue unqualified references when use cells(). vba needs know sheet expect on, when using multiple sheets. i'm assuming cells(i,10) , cells(i,3) on sheet "a". if not, change sheet name:

wb1.sheets("a").cells(i, 10) = application.countifs(wb1.sheets("b").range("b:b"), "*" & wb1.sheets("a").cells(i, 3) & "*")

as can see, that's kind of long , little tricky read. alternative way of doing that, use with:

with wb1.sheets("a")    .cells(i,10) = application.countifs(wb1.sheets("b").range("b:b"), "*" & .cells(i,3) & "*") end 

so, wherever see simple .cells() without before ., it's going use follows with. it's equivalent of formula above...does make sense?