poijnqwedc
11-27-2015, 03:25 PM
I am trying to write a macro that counts the spaces between values. In a column, I have 10,000 rows that I want to account for. Some are blank and some have a value of "1". I am looking to count the spaces in between 1's and display the results.
For example
Row #---------Value
1-----------------1
2-----------------1
3-----------------
4-----------------
5-----------------
6-----------------1
7-----------------
8-----------------1
9-----------------
I would want results to be displayed
0
0
3
1
The first 1 appears in row 1, so the first result value is 0 (no spaces before first 1 appeared). Same case with the second 1. The counter would reset, and there are no spaces before the second 1. The next result value is 3 because there are 3 spaces before the third 1. The next result is 1 because because there is 1 space in between the third and fourth 1. There is no fifth result because there is no fifth one yet. This is what I started to write but I am new to VBA and don't really know what I'm doing.
Dim c As Integer
Dim i As Integer
c = 0
i = 2
While Not Cells(i, 22).Value = "1"
c = c + 1
i = i + 1
Wend
Cells(i, 23).Value = c
Any help would be greatly appreciated!!!!
For example
Row #---------Value
1-----------------1
2-----------------1
3-----------------
4-----------------
5-----------------
6-----------------1
7-----------------
8-----------------1
9-----------------
I would want results to be displayed
0
0
3
1
The first 1 appears in row 1, so the first result value is 0 (no spaces before first 1 appeared). Same case with the second 1. The counter would reset, and there are no spaces before the second 1. The next result value is 3 because there are 3 spaces before the third 1. The next result is 1 because because there is 1 space in between the third and fourth 1. There is no fifth result because there is no fifth one yet. This is what I started to write but I am new to VBA and don't really know what I'm doing.
Dim c As Integer
Dim i As Integer
c = 0
i = 2
While Not Cells(i, 22).Value = "1"
c = c + 1
i = i + 1
Wend
Cells(i, 23).Value = c
Any help would be greatly appreciated!!!!