Counting the number of cells with two digits

vixul

New Member
Joined
Sep 10, 2014
Messages
23
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:


Code:
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:


Code:
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!
You cannot use Len(A2:D5). Len can only be applied to a single cell value. Your rng variable covers multiple cells.
 
Upvote 0
SUMPRODUCT works differently in VBA

Try this

Code:
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) = _
        [COLOR=#0000ff]Evaluate("=SUMPRODUCT(--(Len(" & rng.Address & ") = 2))")[/COLOR] - _
        Application.WorksheetFunction.CountIfs(rng, "SB")
Next x

Hope this helps

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


Code:
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) = _
            [COLOR=#ff0000][B]Application.WorksheetFunction.SumProduct(--(Len(rng) = 2)) - _
            Application.WorksheetFunction.CountIfs(rng, "SB")[/B][/COLOR]
    Next x

Thanks in advance for the help!
Try replacing what I have highlighted in red with the following and see if that does what you want....
Code:
Evaluate(Replace("SUM((@<>""SB"")*(LEN(@)=2))", "@", rng.Address))
 
Upvote 0
SUMPRODUCT works differently in VBA

Try this

Code:
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) = _
        [COLOR=#0000ff]Evaluate("=SUMPRODUCT(--(Len(" & rng.Address & ") = 2))")[/COLOR] - _
        Application.WorksheetFunction.CountIfs(rng, "SB")
Next x

Hope this helps

M.

This gave me a value of 0. (which is not correct)


Try replacing what I have highlighted in red with the following and see if that does what you want....
Code:
Evaluate(Replace("SUM((@<>""SB"")*(LEN(@)=2))", "@", rng.Address))

This just put a #VALUE in the cell.

If it helps at all, the column that rng is referencing contains combinations of two letters as well as numbers (eg. TL, EX, 24, SB). In this example, if rng was only referencing those 4, i would want the formula to return a 3 in the cell (count all cells with 2 digits except for "SB")
 
Upvote 0
Whenever I have trouble mimicing a worksheet formula in VBA.
I find it easiest to first create a working version of the formula in a regular cell formula.
Then go from there.

Can you post a regular formula that you would put in a cell that returns the desired value?
 
Upvote 0
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
Code:
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
 
Upvote 0
Whenever I have trouble mimicing a worksheet formula in VBA.
I find it easiest to first create a working version of the formula in a regular cell formula.
Then go from there.

Can you post a regular formula that you would put in a cell that returns the desired value?

=SUM(1*(LEN(A7:A88)=2)) - COUNTIFS(A7:A88,"SB")

This formula with a CTRL+SHIFT+ENTER so excel knows the first part is an array.
 
Upvote 0
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
Code:
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

You are right , Jonmo

I forgot to include the sheet reference :oops:

Thank you for pointing out

M.
 
Upvote 0
Try replacing what I have highlighted in red with the following and see if that does what you want....
Code:
Evaluate(Replace("SUM((@<>""SB"")*(LEN(@)=2))", "@", rng.Address))
This just put a #VALUE in the cell.

If it helps at all, the column that rng is referencing contains combinations of two letters as well as numbers (eg. TL, EX, 24, SB). In this example, if rng was only referencing those 4, i would want the formula to return a 3 in the cell (count all cells with 2 digits except for "SB")
I think the problem may be in the lack of a reference to the worksheet in my suggested replacement. Try this replacement instead...
Code:
Evaluate(Replace("SUM((@<>""SB"")*(LEN(@)=2))", "@", "'" & Rng.Parent.Name & "'!" & Rng.Address))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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