Excel Macro: insert formula into first blank cell in column & auto
in [Programming]
|
Prev: Getting Data from a 2nd sheet to show in a dropdown box on sheet 1
Next: Excel Macro: insert formula into first blank cell in column &
From: bawpie on 22 Dec 2009 05:46 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!
From: Mike H on 22 Dec 2009 06:03
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! |