macro help please!
 

I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that.
Thanks.


Memo
BAG# 9843 LOCATION# 0000000016
BAG# 1075 LOCATION# 0000000018
BAG# 9841 LOCATION# 0000000016

BAG# 7992 LOCATION# 0000000026
BAG# 9845 LOCATION# 0000000016
BAG# 7990 LOCATION# 0000000026
BAG# 2625 LOCATION# 0000000071
BAG# 1077 LOCATION# 0000000018
BAG# 0136 LOCATION# 0000000061
BAG# 0067 LOCATION# 0000000061

BAG# 0391 LOCATION# 0000000062

macro help please!
 

You need to create a loop that says something like

Dim x as Integer

x = 1

Do Until '''put what you want here

If ActiveCell.Value = "" Then
ActiveCell.Value = x
x = x + 1
End If

ActiveCell.Offset(1,0).Activate

Loop


"pm" wrote:

I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that.
Thanks.


Memo
BAG# 9843 LOCATION# 0000000016
BAG# 1075 LOCATION# 0000000018
BAG# 9841 LOCATION# 0000000016

BAG# 7992 LOCATION# 0000000026
BAG# 9845 LOCATION# 0000000016
BAG# 7990 LOCATION# 0000000026
BAG# 2625 LOCATION# 0000000071
BAG# 1077 LOCATION# 0000000018
BAG# 0136 LOCATION# 0000000061
BAG# 0067 LOCATION# 0000000061

BAG# 0391 LOCATION# 0000000062

macro help please!
 

This little macro will do that. I assumed your data is in Column A starting
in row 2. HTH Otto
Sub FillBlank()
Dim rColA As Range
Dim i As Range
Dim c As Long
c = 1
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
If IsEmpty(i.Value) Then
i.Value = c
c = c + 1
End If
Next i
End Sub

"pm" wrote in message
...
I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that.
Thanks.


Memo
BAG# 9843 LOCATION# 0000000016
BAG# 1075 LOCATION# 0000000018
BAG# 9841 LOCATION# 0000000016

BAG# 7992 LOCATION# 0000000026
BAG# 9845 LOCATION# 0000000016
BAG# 7990 LOCATION# 0000000026
BAG# 2625 LOCATION# 0000000071
BAG# 1077 LOCATION# 0000000018
BAG# 0136 LOCATION# 0000000061
BAG# 0067 LOCATION# 0000000061

BAG# 0391 LOCATION# 0000000062

macro help please!
 

Thanks....That helped...I got it to work.

"akphidelt" wrote:

You need to create a loop that says something like

Dim x as Integer

x = 1

Do Until '''put what you want here

If ActiveCell.Value = "" Then
ActiveCell.Value = x
x = x + 1
End If

ActiveCell.Offset(1,0).Activate

Loop


"pm" wrote:

I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that.
Thanks.


Memo
BAG# 9843 LOCATION# 0000000016
BAG# 1075 LOCATION# 0000000018
BAG# 9841 LOCATION# 0000000016

BAG# 7992 LOCATION# 0000000026
BAG# 9845 LOCATION# 0000000016
BAG# 7990 LOCATION# 0000000026
BAG# 2625 LOCATION# 0000000071
BAG# 1077 LOCATION# 0000000018
BAG# 0136 LOCATION# 0000000061
BAG# 0067 LOCATION# 0000000061

BAG# 0391 LOCATION# 0000000062

macro help please!
 

Thanks. I'll try this one....looks more efficient than mine.

"Otto Moehrbach" wrote:


This little macro will do that. I assumed your data is in Column A starting
in row 2. HTH Otto
Sub FillBlank()
Dim rColA As Range
Dim i As Range
Dim c As Long
c = 1
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
If IsEmpty(i.Value) Then
i.Value = c
c = c + 1
End If
Next i
End Sub

"pm" wrote in message
...
I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that.
Thanks.


Memo
BAG# 9843 LOCATION# 0000000016
BAG# 1075 LOCATION# 0000000018
BAG# 9841 LOCATION# 0000000016

BAG# 7992 LOCATION# 0000000026
BAG# 9845 LOCATION# 0000000016
BAG# 7990 LOCATION# 0000000026
BAG# 2625 LOCATION# 0000000071
BAG# 1077 LOCATION# 0000000018
BAG# 0136 LOCATION# 0000000061
BAG# 0067 LOCATION# 0000000061

BAG# 0391 LOCATION# 0000000062

macro help please!
 

In addition to what was already posted, this one skips the non-blanks:

Dim rng As Excel.Range
Dim cell As Excel.Range
Dim i As Long

Set rng = Range("A2", Range("A" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlan ks)

i = 1
For Each cell In rng
cell.Value = i
i = i + 1
Next cell


HTH,
JP

On Mar 19, 3:44*pm, pm wrote:
I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that.
Thanks.

Memo
BAG# 9843 * * * *LOCATION# 0000000016
BAG# 1075 * * * *LOCATION# 0000000018
BAG# 9841 * * * *LOCATION# 0000000016

BAG# 7992 * * * *LOCATION# 0000000026
BAG# 9845 * * * *LOCATION# 0000000016
BAG# 7990 * * * *LOCATION# 0000000026
BAG# 2625 * * * *LOCATION# 0000000071
BAG# 1077 * * * *LOCATION# 0000000018
BAG# 0136 * * * *LOCATION# 0000000061
BAG# 0067 * * * *LOCATION# 0000000061

BAG# 0391 * * * *LOCATION# 0000000062