Thanks:  0
Likes:  0

# Thread: Counting the number of cells with two digits

1. ## Counting the number of cells with two digits

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(--(Len(rng) = 2)) - _
Application.WorksheetFunction.CountIfs(rng, "SB")
Next x```
Thanks in advance for the help!

2. ## Re: Counting the number of cells with two digits

Originally Posted by 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:

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(--(Len(rng) = 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.

3. ## Re: Counting the number of cells with two digits

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) = _
Evaluate("=SUMPRODUCT(--(Len(" & rng.Address & ") = 2))") - _
Application.WorksheetFunction.CountIfs(rng, "SB")
Next x```
Hope this helps

M.

4. ## Re: Counting the number of cells with two digits

Originally Posted by 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:

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(--(Len(rng) = 2)) - _
Application.WorksheetFunction.CountIfs(rng, "SB")
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))`

5. ## Re: Counting the number of cells with two digits

Originally Posted by Marcelo Branco
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) = _
Evaluate("=SUMPRODUCT(--(Len(" & rng.Address & ") = 2))") - _
Application.WorksheetFunction.CountIfs(rng, "SB")
Next x```
Hope this helps

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

Originally Posted by Rick Rothstein
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")

6. ## Re: Counting the number of cells with two digits

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?

7. ## Re: Counting the number of cells with two digits

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```

8. ## Re: Counting the number of cells with two digits

Originally Posted by Jonmo1
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.

9. ## Re: Counting the number of cells with two digits

Originally Posted by Jonmo1
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

Thank you for pointing out

M.

10. ## Re: Counting the number of cells with two digits

Originally Posted by Rick Rothstein
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))`
Originally Posted by vixul
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))`

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•