Counting the number of cells with two digits

[vixul]

I cannot for the life of me figure out why I am getting a mismatch in the following code. I'm trying to have it just count the number of cells in a range with two digits. I highlighted the spot where I'm getting a mismatch error:

lastrow = foswork.Cells(foswork.Rows.Count, 2).End(xlUp).row
    For x = 1 To 5
        Set rng = foswork.Range(foswork.Cells(1, x + 5), foswork.Cells(lastrow, x + 5))
        staffing.Cells(x + 4, (3 * i) - 1) = _
            Application.WorksheetFunction.SumProduct(--[COLOR=#ff0000](Len(rng)[/COLOR] = 2)) - _
            Application.WorksheetFunction.CountIfs(rng, "SB")
    Next x

Thanks in advance for the help!

 

[vixul]

However, I think the issue with Marcelo's code is that rng.Address does not include the SHEET reference
and rng is built using the sheet foswork...


Try

lastrow = foswork.Cells(foswork.Rows.Count, 2).End(xlUp).Row

For x = 1 To 5
    Set rng = foswork.Range(foswork.Cells(1, x + 5), foswork.Cells(lastrow, x + 5))
    staffing.Cells(x + 4, (3 * i) - 1) = _
        Evaluate("=SUMPRODUCT(--(Len('" foswork.Name & "'!" & rng.Address & ") = 2))") - _
        Application.WorksheetFunction.CountIfs(rng, "SB")
Next x

This worked perfectly! Thanks so much!

I think the problem may be in the lack of a reference to the worksheet in my suggested replacement. Try this replacement instead...

Evaluate(Replace("SUM((@<>""SB"")*(LEN(@)=2))", "@", "'" & Rng.Parent.Name & "'!" & Rng.Address))

This still gave me the #VALUE in the cells, but the other code is doing what I want. Thanks for your help!

 

[Jonmo1]

Glad to help, thanks for the feedback.