Excel Macro: insert formula into first blank cell in column & auto

  • Thread starter Thread starter bawpie
  • Start date Start date
B

bawpie

Okay, I have tried searching the group as well as google but so far I'm
coming up short.

I'm able to put together a macro which would autofill a totally blank column
down to the bottom of the data, however, I'm not sure how to make a macro
that finds the first blank cell in a partially populated column, enters a
formula and then fills it to the bottom.

My spreadsheet is set up in the following way (this is just an example,
there are more columns which the formula uses):

Column A Column B Column C
Client Data Y Office
Client Data N Office
Client Data Y Office
Client Data Home
Client Data Home

Basically, I want to populate the empty cells in column B with a formula -
down to the bottom of client data. I had thought about having a macro that
filters column C on 'Home' (only those with 'home' would be empty) and then
entering the formula into the visible cells on column B but I wasn't sure how
to do that either. This data will change from month to month, so I need a
macro that can identify how to enter the data dynamically rather than setting
a specified range range.

Any help or thoughts on this would be much appreciated,

Thanks!
 
Hi,

It would have helped if you had told us the formula! Try this

Sub FillUp()
Dim lastrowA As Long
Dim lastrowB As Long

lastrowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row
lastrowB = Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lastrowB & ":B" & lastrowA).Formula = "=22/7"
End Sub

Mike
 
Mike H,

Thank you so much, it works perfectly. I probably should have included the
formula, but it was fairly simple so not difficult to add to your example.
Many thanks!
 
Back
Top