Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to add column and insert date then in blank cells copy fromprevious cell | Excel Programming | |||
Auto insert a blank row above the current row based on a cell val. | Excel Discussion (Misc queries) | |||
Macro to insert sum calculation at every second blank cell for data above in column F | Excel Programming | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
? VBA to insert formula if a column is blank or not ? | Excel Programming |