Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Counting the number of cells with two digits

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,502
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting the number of cells with two digits

    Quote Originally Posted by vixul View Post
    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.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default 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. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Counting the number of cells with two digits

    Quote Originally Posted by vixul View Post
    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))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting the number of cells with two digits

    Quote Originally Posted by Marcelo Branco View Post
    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)


    Quote Originally Posted by Rick Rothstein View Post
    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. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    New Member
    Join Date
    Sep 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting the number of cells with two digits

    Quote Originally Posted by Jonmo1 View Post
    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. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Counting the number of cells with two digits

    Quote Originally Posted by Jonmo1 View Post
    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. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Counting the number of cells with two digits

    Quote Originally Posted by Rick Rothstein View Post
    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))
    Quote Originally Posted by vixul View Post
    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))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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