Count number "0" after last "1"

[Undertegnede]

Hi,

I want to be able to count 0's after the last 1 in my spreadsheet. The number i want counted is listed in my "="-column.

Anyone able to assist please?


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tim_Excel_]

Sub findlast()
With Workbooks(REF).Sheets(REF)
Dim findl as Range
Set findl = .Range("A:R").Find("1", matchcase:=True, searchorder:=xlByColumns)

If Not findl Is Nothing Then

    .Range(findl.Row, "S").Value = (18*(findl.Row-1)) - Application.Sum(.Range(.Cells(2, "A"), .Cells(findl.Row, "R"))) 'Where 18.*... is the number of cells columns (A-R) and the sum is the total of 1's

End If

End With
End Sub

Your explanation is a bit contradictory as the totals in your example don't match the values and you mention you only want to count the zeroes after you have found the last "1" (e.g. you only count once, not multiple times like in the example)

 

[Tim_Excel_]

Correction, use

[COLOR=#333333]Set findl = .Range("A:R")[/COLOR].Find("1", MatchCase:=True, searchorder:=xlByRows, searchdirection:=xlPrevious)

 

[FormR]

Hi, do you only have 1's and 0's in the range? If so another option:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	14
2	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	12
3	0	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	7
Sheet1

Cell Formulas
Range	Formula
S1	=COLUMNS(A1:R1)-LOOKUP(2,1/(A1:R1=1),COLUMN(A1:R1)-MIN(COLUMN(A1:R1))+1)

 

[jtakw]

Hi,

Maybe I'm missing something, the last part of the Column math doesn't seem to be needed:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	14
2	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	12
3	0	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	7
Sheet574

Cell Formulas
Range	Formula
S1	=COLUMNS(A1:R1)-LOOKUP(2,1/(A1:R1=1),COLUMN(A1:R1))

 

[XOR LX]

@jtakw

Try moving the data to some different ranges. Your version may give incorrect results, since it is dependent on the data beginning in column A. The version posted by FormR will consistently give correct results.

Regards

 

[jtakw]

Maybe I'm missing something

Thanks, I see now, without the extra Column math, the formula will only work if data started in Column A ( column 1 ) .

 

[Undertegnede]

Thanks for all the input. I have to admit though that with my limited knowledge about vba I was not transfer the code to use for my actual dataset.

https://1drv.ms/u/s!ArNOSClmeB5wgYsx-7fXiEqqedNECw

I have placed a picture here showing my actual dataset. Sheetname could be Sheet1. I will also be expanding the included columns as time goes by, but figure I will understand how to adjust the code to fit with the changes.

Two main questions:

- Would anyone be so kind to adjust the code to fit with my dataset? I apologize for not providing this at first.
- Would this code need to be executed, or is the thought that it would run automatically?

Thanks again!

 

[FormR]

Hi, I can't speak of the VBA - but did you try to adapt the formula in post #4 to your actual set-up?

 

[Rick Rothstein]

Thanks for all the input. I have to admit though that with my limited knowledge about vba I was not transfer the code to use for my actual dataset.

https://1drv.ms/u/s!ArNOSClmeB5wgYsx-7fXiEqqedNECw

I have placed a picture here showing my actual dataset. Sheetname could be Sheet1.

Your thread title indicates you would have zeros in the non-one cells, but your picture shows dashes instead. The following UDF (user defined function) will work as long as there is something in those cells (in other words, as long as the non-one cells are not blank)...

[table="width: 500"]
[tr]
	[td]Function CountAfterLastOne(HorizontalRange As Range) As Long
  Dim Dashes() As String
  Dashes = Split(Join(Application.Index(HorizontalRange.Value, 1, 0), ""), "1")
  CountAfterLastOne = Len(Dashes(UBound(Dashes)))
End Function[/td]
[/tr]
[/table]