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

[jorel]

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

[kvsrinivasamurthy]

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"))

[6StringJazzer]

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.

[kvsrinivasamurthy]

Macro code

Please Login or Register  to view this content.

[dangelor]

Possibly...

Please Login or Register  to view this content.

[jorel]

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

[jorel]

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

[Rick Rothstein]

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?

[jorel]

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

[Rick Rothstein]

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.

[6StringJazzer]

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.)

[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

[jorel]

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

[jorel]

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

[jorel]

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?

[Rick Rothstein]

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.

[jorel]

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

[AliGW]

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.

[6StringJazzer]

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.