numbering items in a column based on a condition

[pacman2k4]

I need a procedural method to number a column for an entire worksheet
based upon a condition.

So, if text exists in column B?, then add a sum to A? cell next to it.

Column A Column B
1 Text
2 Text
No Text
3 Text
4 Text

It has to be smart enough to number all columns in all worksheets.

So, if the last number in Worksheet A was 50, then the next number in
Worksheet B would start at 51.

 

[Bob Kilmer]

I find your problem statement a little confusing, but here is a brute force
approach to numbering rows in A where text occurs in B.

Option Explicit

Sub Main()
Dim wks As Worksheet
Dim cell As Range
Dim count As Long
Dim oldStatusBar As Boolean
count = 0
Application.ScreenUpdating = False
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True

For Each wks In Worksheets
For Each cell In Intersect(wks.Columns("B").Cells, wks.UsedRange)
If Len(cell.Text) > 0 Then
count = count + 1
cell.Offset(0, -1).Value = count
Application.StatusBar = count
End If
Next cell
Next wks

Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
Application.ScreenUpdating = True
End Sub

 

[Bob Kilmer]

Following doesn't choke on blank sheets:

Option Explicit

Sub Main()
Dim wks As Worksheet
Dim cell As Range
Dim rng As Range
Dim count As Long
Dim oldStatusBar As Boolean
count = 0
Application.ScreenUpdating = False
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True

For Each wks In Worksheets
Set rng = Intersect(wks.Columns("B"), wks.UsedRange)
If Not rng Is Nothing Then
For Each cell In rng.Cells
If Len(cell.Text) > 0 Then
count = count + 1
cell.Offset(0, -1).Value = count
Application.StatusBar = count
End If
Next cell
End If
Next wks

Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
Application.ScreenUpdating = True
End Sub

 

[pacman2k4]

Worked like a charm. Where should I send a check to