Macro based on cell value

I'm struggling with the following problem:

In the attached file, I would need a macro that would expand the formula in D4 to all cells in column D if the cell on the same row in column B is not empty. Of course, this should only apply to the cells D4:D35, D43:D74 and D82:D113. In between, the value in the cell in column D should not be changed.

It should also work in case a row is inserted or deleted (and that's the problem I'm struggling with; I managed to get Macro 1 work, but it fails if I remove or insert a row).

If someone could help me out, I'd be very grateful :-)

Macros generally don't respond as well as formulae when it comes to inserting/deleting rows. Why not use a formula? Wouldn't putting the formula below in D4 and filling down to D35 (then pasting and filling down through your other ranges) get you the desired result, macro-free?

=IF(ISBLANK($B4),"",VLOOKUP($B4,Table!$A$2:$B$78,2,FALSE))

I know, but my intention is not to need any formula fixing when inserting a row (other people have to work with the file too, and I don't want them to screw with the formulas) :-)

Where will users be inserting rows?

Between rows 4 and 35, between 43 and 74 and between 82 and 113.

If I understand correctly, then if all they're doing is adding data to previously empty rows, they don't need to touch the formula at all. Fill it all the way from D4:D35. When the row is blank, the cell in D will be blank. When they add an entry to B, D will make the VLOOKUP automatically. Try experimenting with the attachment, let me know how it does/doesn't work.

Well, if all lines are used, and a line has to be added for instance between row 10 and 11, the cel in column D in the new row will be empty. And I'd like a macro so I can make a button that someone just has to press if they add a row instead of copying the formula.

I see now. In the real database, will there always be a date in column A if there's an entry in column B?

Unfortunately, no. A date will only be in the database once, and the rest of the actions on that date are added below, without a date.

Ok, try this one - run from your data page, not the table.

Please Login or Register  to view this content.

Thank you so much!

Unfortunately I don't really understand it, but it works

Glad I could help. It basically loops through all of your rows and, where B is not empty but C and D are, it stops and plants your Vlookup formula in D. Will all values in column B have a corresponding value in the "table" worksheet? If not, we'll need to add an error handler based on what you want to happen for those cells.

Well, if there is no corresponding value, I'd like a certain text (like 'missing' or 'error') in that cell.

Thanks again :-)

Try this one. You can replace "Missing" if you want something else entered when there are failed matches.

Please Login or Register  to view this content.

You could go with this version, which puts the actual formula in column D, so it will respond to any changes in "Table":

Please Login or Register  to view this content.

The procedure looks for rows where C is blank but B is not. The goal is to avoid your subtotal rows, so keep the conditions in mind if you make any changes.

Did I already mention you're awesome? :-)