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

1. ## 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

2. ## 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"))

3. ## 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.

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

Macro code
``Please Login or Register  to view this content.``

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

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

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: macro that detects the triplets that have the digit zero (red color) .

Originally Posted by jorel
error
Sub Sum_Zeroes()

Welcome to the forum.

(Note: this change is not optional.)

12. ## 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. ## Re: macro that detects the triplets that have the digit zero (red color) .

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

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

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

15. ## 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. ## 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. ## 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. ## Re: macro that detects the triplets that have the digit zero (red color) .

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.

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

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

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

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