Write a macro to number cells...?

I am trying to write a macro that will number each cell in a column (1,2,3,4,5...) until the cell to the right of it is empty.

The cells need to be numbered in column A and it must always check column B to see if it is empty or not. The cells being numbered must begin at the ActiveCell and the range of cells being numbered will change. So if B1 is not empty paste "1" in A1 then if B2 is not empty paste "2" in A2...etc until Bsomething is empty.

Example:

Column A Column B

1 Apple

2 Bananna

3 Cay

4 Dog

5 Elf

6 Fruit

1 Apple

2 Bananna

3 Cat

1 Apple

2 Bananna

3 Cat

4 Dog

I was thinking a Do...loop with an If statement embedded.

Help...thank you.

1 Answer

Relevance
  • 1 decade ago
    Favorite Answer

    You could probably use any number of different approaches to a macro to do this. Here is one way.

    Sub NumberColA()

    Dim i, LastRow

    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow

    If Cells(i, "B").Value <> "" Then

    If Cells(i, "B").Row = 1 Then

    Cells(i, "A") = 1

    Else

    Cells(i, "A") = Cells(i - 1, "A") + 1

    End If

    End If

    Next

    End Sub

Still have questions? Get your answers by asking now.