Drag a formula down by a macro, after a set of actions

Hello guys!

I'm sorry in advance for not knowing some of the technical language, because I'm Dutch.

I'm new here, have a lot of experience with Excel, but when it comes to Macro's I'm a bit lost. So I try to pick up some knowledge about them. For an assignment I have to adjust an Inventory tool that was partially build by someone else. I have a question regarding a new function in this tool:

To add a new product to the field I use the command button "Artikel toevoegen (Add product)" which opens a UserForm. In this Form I fill in the product information, after which I press "Aanmaken nieuw artikel (Make a new product)". After pressing that button, I would like the macro to drag down the formula (or add it to) in column H for the new product. This column represents the status of the product in the inventory.

I'm looking forward to your ideas!

Regards, Victor

Will column H ever have blanks or should it always have formulas down to the last row of products that are input?

It should always have a status when a product and product information is next to it. When there is no product information in the same row, there should not be a status.

Alrighty then, lemme work on it for you. I think best solution is formula. What if you included an IF before your formula to leave H25 blank unless there is data in A25. The formula could be all the way down column H.

This is the formula modified as I mentioned previously

After I posted that reply I re-read your post a see you particularly asked for VBA solution.

Let me know if this formula will not work as you need it to and I'll try for VBA solution.

Please Login or Register  to view this content.

Yeah, I used a formula before as well, but that gave me another problem. When choosing a product from the drop down "Omschrijving (description)" after using the command button "Ontvangsten / Uitgifte" it also gives me the options of the blank pages (where there's no products) with that status formula. For that reason I would like to integrate it in a macro, so that when I add another product, the status also appears and the drop down would still be 'clean'.

No problem, I am currently working on a similar issue on my workbook. I will try to get to it soon. Will try to post solution today.

For clarity - If data is in A* then formula should be in H*, or if any column has data A thru G, H should have formula.

Yep, that's right. Thanks for the help so far

Kind of found the solution. It's not exactly what I wanted since it checks all of the rows instead of adding just one row with the formula, but it gives me the same result, so I'm happy!

I used:

Formula:

Please Login or Register  to view this content.

I have another solution for you unless you are happy with what you have.

My solution inserts a row, copying down formulas and formatting from the previous row, and clearing any text from the copied range.

At this moment I am happy with the solution, but if you would like to post the code I will take a look!

This is what I use to insert rows. You need a method to find the last row of your sheet. I use this which works for me

Please Login or Register  to view this content.

This part I use to ask how many rows I want to add (my workflow is different than yours. I sometimes add 100 rows at a time for my process

Please Login or Register  to view this content.

This next part:

1. selects the row the previous code determined is the last line of data based only off column A
2. inserts a row
3. copies down the last row including formula
4. clears data from new row leaving formatting and formulas.

Please Login or Register  to view this content.

I have another solution for you.
Solution #1 If you convert your data to a table, the formula will automatically carry down with each new line of data you enter without the need for VBA or modifying your formula.
1. unprotect your sheet
2. select the range of your data A1:H24
3. select insert from ribbon
4. select table on left side
5. click ok

Now enter data in userform and formula should automatically carry down. There are some things affected when changing your data to a table. You can research that and see if it will affect you in any way.

The other solution I have is VBA however if you already have VBA solution posted then there is no need to post.