+ Reply to Thread
Results 1 to 19 of 19

macro that detects the triplets that have the digit zero (red color) .

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    macro that detects the triplets that have the digit zero (red color) .

    macro that detects the triplets that have the digit zero (red color) and add the amount and separate in a list on the side
    ex=450, yes has zero
    458 = no, there is no zero
    only the one with the zeros
    https://www.excelforum.com/attachmen...1&d=1628775788
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    7,016

    Re: macro that detects the triplets that have the digit zero (red color) .

    Formula help.
    In D6

    =IF(COUNTIF(A6:C6,0)>0,SUM(A6:C6),"")

    For consolidated sum

    =SUMPRODUCT((COUNTIF(OFFSET($A$6:$C$6,ROW($A$6:$A$1007)-ROW($A$6),0),0)>0)*SUMIF(OFFSET($A$6,ROW($A$6:$A$1007)-ROW($A$6),0,1,3),">0"))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,609

    Re: macro that detects the triplets that have the digit zero (red color) .

    It is not hard to detect the triplets that have a zero, but it is not clear what result you want.
    add the amount and separate in a list on the side
    Can you be more specific about this? It would help to show an example of what you want the result to look like.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    7,016

    Re: macro that detects the triplets that have the digit zero (red color) .

    Macro code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,162

    Re: macro that detects the triplets that have the digit zero (red color) .

    Possibly...
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    error
    Sub Sum_Zeroes()
    Dim rg As Range
    Dim i As Long, j As Long, x As Long


    Set rg = Sheet1.Cells(6, 1).CurrentRegion
    For i = 1 To rg.Rows.Count
    For j = 1 To 3
    If rg(i, j) = "0" Then
    x = x + CLng(rg(i, 1) & rg(i, 2) & rg(i, 3))
    j = 3
    End If
    Next j
    Next i
    MsgBox "The sum total of all 3 digit numbers with a zero is " & Format(x, "#,##0")
    End Sub

  7. #7
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    hello, I'll explain better, the macro will detect the formations that have zero and separate them in a list on the side, the formations that have zero are already in red, you have to separate or filter which ones have zero later, separate them in a list on the side, like 2255 if in all there are 1000? should be around 200
    formations, the objective is to have this formations with zero
    separated

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,197

    Re: macro that detects the triplets that have the digit zero (red color) .

    Will your original data have cells in them with text instead of numbers like at the bottom of the data shown in your attachment? Or will your data always be numbers and no text?

  9. #9
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    but it is only necessary to separate formations that have the zero digit ex 450 609 058

  10. #10
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,197

    Re: macro that detects the triplets that have the digit zero (red color) .

    I know, but the code I am going to suggest will "choke" on blank cells and text so I need to know if I have to build in a trap for non-numeric data.

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,609

    Re: macro that detects the triplets that have the digit zero (red color) .

    Quote Originally Posted by jorel View Post
    error
    Sub Sum_Zeroes()

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional.)

  12. #12
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    but the macro is not mine, I just showed that when I go to activate it gives an error, the amcro belongs to one who posted

  13. #13
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    Set rg = Sheet1.Cells(6, 1).CurrentRegion error dangelor

  14. #14
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    Set rg = Sheet1.Cells(6, 1).CurrentRegion = error

  15. #15
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    but, I just want you to separate formations that have the digit zero that is already in red in a list what's the doubt?

  16. #16
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,197

    Re: macro that detects the triplets that have the digit zero (red color) .

    Since you would not answer my direct question, I assumed there could be text and/or empty cells within the range (they will be ignored). Given that, this macro (as written) will output the list you want to Columns F:H and output the sum you want to cell I6...
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    1,963

    Re: macro that detects the triplets that have the digit zero (red color) .

    ok RICK , great job i will also use digit one (1) i will use the same formula, it is a filter on benford's law for pick 3, great job, congratulations, thanks rick

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,380

    Re: macro that detects the triplets that have the digit zero (red color) .

    Administrative Note:

    Sorry, but your post does not comply with Rule #6 of our Forum RULES:

    Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction, then send a private message to them asking for clarification.

    All Participants:

    Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  19. #19
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,609

    Re: macro that detects the triplets that have the digit zero (red color) .

    Quote Originally Posted by jorel View Post
    but the macro is not mine, I just showed that when I go to activate it gives an error, the amcro belongs to one who posted
    It doesn't matter who wrote the code. When you put code in a post we require code tags.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-08-2019, 07:53 PM
  2. Macro to copy and paste row if macro detects movement
    By 1800builder91 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2018, 10:49 AM
  3. Macro that detects if specific cells are blank, does a pop up
    By lora2014 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2015, 11:48 PM
  4. [SOLVED] Macro that detects certain value and replaces value
    By keis386 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-28-2013, 11:54 AM
  5. Macro to copy rows if detects a specfic key word
    By Misbah211 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-22-2012, 10:38 AM
  6. Macro that detects the source files and lists all the formulas
    By triplemints in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2010, 07:48 PM
  7. [SOLVED] Color a single digit in a mult-digit number cell
    By Phyllis in forum Excel General
    Replies: 6
    Last Post: 11-16-2005, 08:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1