macro help please!

P

pm

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
 
A

akphidelt

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
 
O

Otto Moehrbach

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
 
P

pm

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

akphidelt said:
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
Click to expand...
 
J

JP

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(xlCellTypeBlanks)

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


HTH,
JP
 
Top