PDA

View Full Version : [SOLVED] Count Spaces Between Values



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

SamT
11-27-2015, 04:30 PM
This only counts blanks, so any value will record the count and start from 0. It also counts Row 1 if it is a Blank.
Option Explicit

Sub VBAX_SAMT_SpaceCounter()
Dim Cel As Range
Dim i As Long
Dim LR As Long

LR = Cells(Rows.Count, 22).End(xlUp).Row
Set Cel = Cells(1, 22)

Do While Cel.Row <= LR
If Cel <> "" Then
Cel.Offset(, 1) = i 'Record Count in Column 23
i = 0
Else
i = i + 1
End If

Set Cel = Cel.Offset(1)
Loop


End Sub

poijnqwedc
11-27-2015, 04:59 PM
Works Perfect. Thanks so much Sam!!!