Posts by malathion

    Re: Selecting and Deleting every 24th row?


    I haven't tested this but it should work. BACK YOUR DATA UP BEFORE YOU RUN ANYTHING LIKE THIS.



    Also, beware of the fact that after you delete a row, the index of all the rows beneath it will change. You may need to increment your index after deleting the row. The code I wrote will not do that since I didn't know which behavior you wanted.

    Re: when a number reaches "X" the cell defaults to zero and adds one to another cell


    In the code you pasted you were modifying H15 not F15. I changed it below.



    Re: when a number reaches "X" the cell defaults to zero and adds one to another cell


    Quote from craigswear;628621

    I need a VBA code that will meet the following conditions: when cell g15 goes higher than 15 it should return to zero and start over again. At the same time it should add 1 to cell f15. Any ideas?


    Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    It does need to be generic. Now that you've replied I've realized how I'm going to do this, and it will be easy enough that I'm amazed this hasn't been published already. I plan to post it back here once I get it working, but in case I forget, here's the outline I have in mind:


    My VBA code will read the existing conditional formatting rule on the cell. It will test the cell to see if the condition was matched. If it was, it will apply the formatting.


    Luckily with some googling I am halfway there, since someone has developed a method for reading cell color regardless of whether it was set by cell formatting or conditional formatting: http://www.vbaexpress.com/kb/getarticle.php?kb_id=190


    I will report back.

    Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    Quote from AAE;614528

    Yep. Start with the macro recording going through the steps of:


    Selecting the cell range
    Deleting all Conditional Formats
    Applying formats equal to what was applied in the CF condition.


    Afterwards, post your code and workbook (dummy data) for helping in refining the code.


    I must have misunderstood you because this gave me code for deleting all the conditional formats and then setting arbitrary formatting. I need a way to copy the result of the conditional format to the cell format programattically, and cell by cell.


    Quote from mikerickson;614529

    What version of Excel are you using?


    2010

    Re: Creating New Dynamic Ranges After Copying a Worksheet


    Something like this, haven't tested though


    My company represents a major steel manufacturer. I have a workbook that draws data from an Access database into a table on Sheet 1. That table then feeds a pivot table on Sheet 2 with columns organized by ship date -> PO number -> $ value. The PT sums the values of our open orders by month and uses a calculated field to show the difference between the production budget for the month (set in dollars) minus the orders already booked for production.


    The problem is that right now, I am using a constant as the budget for every month that approximates the actual available production capacity. But in reality this number is volatile. So what I want to do is figure out some method to display available production capacity where the budget is also variable and can be set by the user (me).


    My first try involved adding a separate table and adding the budget for the month as its own column in the table on Sheet 1. I thought I could then use the calculated field. Of course this didn't work since the sum for the monthly budget gets multiplied by the number of open orders for that month, so the production capacity is grossly over-valued.


    Any suggestions as to how to implement this?