Find Empty cell enter value then continue to next

[CrashOD]

Find First Empty cell in range A:A (say does end A10) and count only the none Empty cells and enter value to B1.
Then continues down (Say Ends A22) and count only the none Empty cells and enter value to B2.
Ect. Till end of book.
So for this B1 would have 9 since A10 is empty and B2 would have 11 since A11 - A21 have data.

Thanks so much!

 

[shaowu459]

try this:

工作簿1
	A	B	C	D
1	T			9
2	T			11
3	T			8
4	T			0
5	T			0
6	T			0
7	T			0
8	T			0
9	T			0
10				0
11	T			0
12	T			0
13	T			0
14	T			0
15	T			0
16	T			0
17	T			0
18	T			0
19	T			0
20	T			0
21	T			0
22				0
23	T			0
24	T			0
25	T			0
26	T			0
27	T			0
28	T			0
29	T			0
30	T			0
31				0
32				0
33				0
34				0
35				0
Sheet1

Cell Formulas
Range	Formula
D1:D1048549	D1	=FREQUENCY(IF(A:A<>"",ROW(A:A)),IF(A:A="",ROW(A:A)))
Dynamic array formulas.

 

[CrashOD]

this is the file and sheet your code but modified is going in to
File Name: ECT - Report - Copy.xlsm
Sheet: Recepit Ledger
D is the row it is to look and count.
=FREQUENCY(IF('[2022 - ECT - Real Estate - Copy.xls]RPT 1'!DD)<>"",ROW('[2022 - ECT - Real Estate - Copy.xls]RPT 1'!D:D)),IF('[2022 - ECT - Real Estate - Copy.xls]RPT 1'!D:D="",ROW('[2022 - ECT - Real Estate - Copy.xls]RPT 1'!D:D))) ' in column wanting cells before space count

is were it is counting from
File Name: 2022 - ECT - Real Estate - Copy.xls
Sheet Name: RPT 1
i get error
#NAME?

also is there away to tell what is the above the empty line. so if a D will display count in the cell else 0 or nothing.
I would then modify code for the different cells in D = Discount, F = Face, P = Penalty
File Name: ECT - Report - Copy.xlsm
Sheet: Recepit Ledger

 

[shaowu459]

try remove （） around B

 

[CrashOD]

When I took that off like you said I now get an error code I've never seen before. #SPILL
I tried using the dollar sign before each of the letters and that didn't seem to help either.

 

[shaowu459]

When I took that off like you said I now get an error code I've never seen before. #SPILL
I tried using the dollar sign before each of the letters and that didn't seem to help either.

you are using Office 365? try this one:

=LET(t,FREQUENCY(IF(A:A<>"",ROW(A:A)),IF(A:A="",ROW(A:A))),FILTER(t,t))

 

[CrashOD]

THAT WORKED GREAT THANKS SO MUCH!!!!!
do you know if i have to start a new post for a different line of code?
=IF(B3<>"", '[2022 - ECT - Real Estate - Copy.xls]RPT 1'!A(B3)+3, "")
I get #NAME?
Should be
3/1/2022 (from the [2022 - ECT - Real Estate - Copy.xls]RPT 1) B3 from current workbook + 3 for cell location on the [2022 - ECT - Real Estate - Copy.xls]RPT 1 in row A.

 

[shaowu459]

THAT WORKED GREAT THANKS SO MUCH!!!!!
do you know if i have to start a new post for a different line of code?
=IF(B3<>"", '[2022 - ECT - Real Estate - Copy.xls]RPT 1'!A(B3)+3, "")
I get #NAME?
Should be
3/1/2022 (from the [2022 - ECT - Real Estate - Copy.xls]RPT 1) B3 from current workbook + 3 for cell location on the [2022 - ECT - Real Estate - Copy.xls]RPT 1 in row A.

how about:
=IF(B3<>"", '[2022 - ECT - Real Estate - Copy.xls]RPT 1'!B3+3, "")

 

[CrashOD]

that brought in a long number. but the B3+3 i changed to A(B3)+3 for your code and i get name error again. it is in row A and i want to use b3 so A would be = to A8+3 for cell A11 after it is done with A8 + 3
so
=IF(B3<>"", '[2022 - ECT - Real Estate - Copy.xls]RPT 1'!A(B3)+3, "")

so it works. it is bringing the bill number in plus 3 to bill number but need it to pull date in and call row A11 or what ever b3 for a number +3.
so if b3 has 10 the needs at call A13.

thanks

 

[CrashOD]

=IF(B4<>"", '[2022 - ECT - Real Estate - Copy.xls]RPT 1'!A("B4"+2,1), "")
NAME ERROR.