Counting cells in a column to add formulas to that equal number of cells

**flipjarg** · 07-11-2012, 10:34 AM

What I am trying to do is count how many cells have data and then drop in formulas in some cells to the right, according to how many cells I have counted as NOT being empty. Attached is a picture that may more clearly explain what I am trying to do.

There must be a better way to do this... because what I have isn't working this is where my macro is running into problems:

...
    ' COUNT THE NUMBER OF CUSTOMERS AND SAVE AS A VARIABLE
    Range("B2").Select
    Do While IsEmpty(ActiveCell.Value) Or ActiveCell.Value = ""
        custCount = custCount + 1
        n = n + 1
        ActiveCell.Offset(1, 0).Select
    Loop
    ' MsgBox "n = " & n & " ..... custCount = " & custCount, vbInformation, "VARIABLES AFTER WHILE LOOP"
    ' MsgBox "C" & custCount, vbInformation, "Test"
    
    ' FILL CARRIER CELLS WITH FORUMLAS AND QUANTITY CELL WITH 1s
    For n = 0 To custCount
        Range("C2", "C" & custCount).Select
        ActiveCell.FormulaR1C1 = "1"
        n = n + 1
    Next n

**StevenM** · 07-11-2012, 11:07 AM

Maybe something like:

Sub FindItemsInColumn()
    Dim nRow As Long
    Dim nLastRow As Long
    
    ' Find last row with data in column B
    nLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For nRow = 2 To nLastRow
        Range("C2:C" & nRow).FormulaR1C1 = "1"
    Next nRow
End Sub

**flipjarg** · 07-11-2012, 11:30 AM

Hey, this works great! Now I just need to do some reading so I understand how it works!

**StevenM** · 07-11-2012, 11:45 AM

nLastRow = Cells(Rows.Count, "B").End(xlUp).Row

In an Excel Spread sheet use Ctrl + an arrow key. Up, down, left, and right.

Rows.Count is the last row in a spread sheet.

Cells(Rows.Count, "B") is the last cell in column B.

End works like Ctrl+ arrow key.

End(xlUp) = Ctrl + up arrow key.

So Cells(Rows.Count, "B").End(xlUp) is the last cell in column B with data.

.Row gives the row where that cell is located.