Turn formula into macro

My goal is for a macro to automatically select the range of cells below Door Size and Qty in this formula.

=SUM(E2:E5)+SUMPRODUCT((LEFT(D2:D5,1)="(")*(E2:E5))

Where E2:E5 represents the full range of numbers below "Qty" and D2:D5 represents the full range of numbers below "Door Size".

The order of columns and number of rows is subject to change so the macro should automatically find "Door Size" and "Qty", select the range of numbers below, and apply these ranges to the above formula. The purpose of the formula is to double the quantity of doors at pairs, where the door size starts with a "(".

I have a similar macro that finds "Door Size" and highlights the entire column, but I need the macro to only select the range underneath "Door Size" and stop once it hits a blank. And then do the same thing for "Qty" and insert those ranges into the formula above.

Here's the macro to highlight the door size column.

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Please Login or Register  to view this content.

This Version will run whenever you make a change on the sheet.

Right Click on the sheet name at the bottom of excel and select view code.

Paste the code in the module that opens and then close it.

Please Login or Register  to view this content.

Thank you for the response! I've slightly adjusted the macro so that it will fit more ranges of data. The column headers aren't always in the first row so I changed the range to 1:15 (the headers are seldom if ever below row 15) and this worked. However, I'm hoping you can help me refine the formula. Instead of R2C, I'd like the "2" to automatically be the row below the header.

I store my macros in a hidden PERSONAL Excel Binary Workbook in the XLSTART folder so that the few macros I run are always available in every new workbook. I think I can store this Macro in that same workbook and then if the range changes I'll just re-run the the macro which would eliminate the need for "Private Sub Worksheet_Change". Do you think that'll work? Seems to be working right now. If there's something from the 2nd macro that I should be including in the 1st please let me know.

Finally, would it be possible to add "Total Door Leafs" in the cell next to the sumproduct? If not, I'll manually type that in each time.

Please Login or Register  to view this content.

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)

Please Login or Register  to view this content.