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

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!

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

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

"bawpie" wrote:

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!

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

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!

"Mike H" wrote:

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

"bawpie" wrote:

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!