Number Cells skip blank cells, Excel 2000 & 2003

[jfcby]

Hello,

How can the following macro be modified to skip blank cells:

Sub NumberCellsValue()

Dim LastRow As Long
With ActiveSheet

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = False
Selection.ColumnWidth = 5

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("a1").Value = 1
With .Range("a2:a" & LastRow)
.FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)"
.Value = .Value
End With
.Range("a:a").NumberFormat = "000"
End With
Range("A1:A3").ClearContents
Range("A1").Select
End Sub


Thank you for your help,
jfcby

 

[Guest]

Why don't you explain what you want to do. The code you have posted won't do
very much of anything except throw error messages.

 

[jfcby]

Hello JLGWhiz,

Thank you for your help!

In my worksheet beginning with A4,
if B4 = B5 Then A4 = 1
if B5 = B6 Then A5 = 2
if B6 isBlank Then A6 isBlank
if B7 isNotEqual to B8 then A7 = 1
if B8 isNotEqual to B9 then A8 = 1
if B9 = B10 Then A9 = 2
if B10 = B11 Then A10 = 3

Thank you for your help,
jfcby

 

[jfcby]

Hello JLGWhiz,

Thank you for your help!

In my worksheet beginning with A4,
if B4 = B5 Then A4 = 1
if B5 = B6 Then A5 = 2
if B6 isBlank Then A6 isBlank
if B7 isNotEqual to B8 then A7 = 1
if B8 isNotEqual to B9 then A8 = 1
if B9 = B10 Then A9 = 2
if B10 = B11 Then A10 = 3

Thank you for your help,
jfcby

 

[jfcby]

Hello JLGWhiz,

I apologize for not giving more details in my earlier post. Thank you
for your help.

In my worksheet my data looks like this:

Col A is empty

Col B
B4 AIR COMP
B5 AIR DRYER
B6 CAB HEATER
B7 CAB HEATER
B8 CAB HEATER
B9 CAB HEATER
B10
B11 COIL, REHEAT
B12 DKFTN
B13 DKFTN
B14
B15

Beginning with A4,
if B4 = B5 Then A4 = 001
if B5 <> B6 Then A5 = 001
if B6 = B7 Then A6 = 001
if B7 = B8 Then A7 = 002
if B8 = B9 Then A8 = 003 and A9 = 004
if B10 isBlank Then A10 isBlank
if B11 <> B12 Then A11 = 001
if B12 = B13 Then A12 = 001 and A13 = 002
if B14 isBlank Then A14 isBlank
if B15 isBlank Then A15 isBlank

What I need to do is number the items in column b that are the same
begininng with 001 in Col A as described above skipping blank cells.

I changed the above macro to:

Sub NumberCellsValue2()
'Number cells 001

Dim LastRow As Long
With ActiveSheet

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = False
Selection.ColumnWidth = 5

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For Each cell In .Range("A4:A" & LastRow)
cell.Select
If cell.Value = "" And cell.Offset(, 1).Value = "" Then GoTo
NX
If cell.Offset(, 1).Value = cell.Offset(1, 1).Value Then
If cell.Offset(, 1).Value <> cell.Offset(-1, 1).Value And
_
cell.Offset(, 1).Value = cell.Offset(1, 1) Then
cell.Value = 1
ElseIf cell.Offset(, 1).Value = cell.Offset(-1, 1) And _
cell.Offset(, 1) = cell.Offset(1, 1) Then
cell.Value = cell.Offset(-1) + 1
End If
ElseIf cell.Offset(, 1).Value = cell.Offset(-1, 1) Then
cell.Value = cell.Offset(-1) + 1
ElseIf cell.Offset(, 1).Value = "" Then
'do nothing
ElseIf cell.Offset(, 1).Value <> cell.Offset(1, 1).Value
Then
cell.Value = 1
End If
NX:
Next
'End With
.Range("a:a").NumberFormat = "000"
End With
Range("A1").Select
End Sub

Can this cade be modified without using so many if statements? If not,
it works fine.

Thank you for your help,
jfcby