Posts by celeb

    roy


    The code you suggested above doesn't work - the #REF! errors remain in the code and it appears to skip over replacing them. My original code did work - it was just super slow, I suspect because of the the 'For each calc' part.


    Carim


    Your code returns an error 'No cells were found' despite there clearly being #REF! errors in cells. I suspect however even if your code were to run, it would actually wipe the formula from the cell? I wish to retain the formulas, just remove the #REF! error inside it.


    I was thinking something like the below would work however I still get the same error of 'No cells were found' if I try to replace all 4 lines of original code. It's almost as if it's not searching the formulas within the range properly.

    Code
    .Range("D10:G5010").SpecialCells(xlFormulas, xlErrors).Replace "#REF!", "0", xlPart

    Apologies Roy, for some reason there is corrupted data in that upload. I've uploaded again here as a new file and with fewer worksheets.


    The current method I have does work - I'm really wondering if there's a faster way to do it. I assume the reason it's currently so slow is because it's individually looking at each cell in the range and writing over the #REF error with '0'. Is there a way I could do this with an array or something that would be faster/more efficient?

    So after royUK kindly helped me get some bits of my macro working, I'm now looking at how I can run my macro more efficiently.

    Using a steptimer method (in the module called 'Check'), I've managed to work out that >95% of the time it takes my code to run is dedicated to 4 lines of code.


    The full code is:


    With the AppSetting code provided by Roy:


    My code is being slowed by this segment:


    Code
    'Remove #REF! errors that have resulted due to deleting columns non relevant to sheet, however retain formula    
    Dim Calcs As Range
    For Each Calcs In .Range("D11:G5010").SpecialCells(xlFormulas)
    Calcs.Formula = Replace(Calcs.Formula, "#REF!", "0")
    Next Calcs

    Just prior to this segment of code, there is a section which deletes a number of columns which are not specific to the worksheet currently being worked on (and range of columns can change for each worksheet). As a result, the Formulas in D11:G5010 end up with #REF errors through them. I want to remove the reference error (currently being done by replacing it with 0) and retain the formula in the cell (as opposed to deleting the formula or cell).


    Any suggestions on how to do this more efficiently?


    I've also attached a sample of the workbook with most of the loop worksheets deleted to reduce file size and time to execute.

    Thanks Roy - much much faster.


    Is there a way that I could edit this part of the code:


    So that the first section of the code for copying across data will only copy if the row it is copying from contains text (in Column W, the quarter the data applies in) that partially matches the name of the worksheet it is copying to (specifically if it equates to the first two letters of the worksheet name)? If I can do that, then I should also be able to remove the Union function.


    I want to only copy across the data if it is relevant to that quarter - at the moment all data copies to all quarters.


    This is what the text in the current code indicates I'm trying to do but haven't figure out yet - I was thinking something along the lines of a multiple If statement but couldn't figure out how make it work for reading and matching each row of Column W to the worksheet name:


    Code
    'Read the year something applies in from Column W and only copy the data for those years.
    'Nest within this functionality, the ability to then read which quarter (Q1-Q4) the data should apply in and only copy to those quarters
    'Use Else such that if it is blank or has N/A is just copies to all (so that the delColumn later still works, unless there is a way to not copy non-required rows)
    
    '   If (Cell W right(2)) <= Right(.Name, 2) Then (do not copy)
    '   ElseIf (Cell W) = "N/A" Then (copy to all)
    '   Else (could maybe get rid of else if and just copy to all)
    'End If

    No unfortunately it doesn’t work as required yet. I still don’t know how to get the code to do two things which can be summarised as follows:


    Column W captures in which Quarters, the event has an impact. For some events, they only impact in certain quarters (i.e the weather line which only impacts operations in Q1 and Q4), but others may impact across all (or only sporadic) quarters.

    Column X captures when the impact is expected to end. For weather - there is no end. But for other issues (Covid for example), there's expected to be no ongoing inpact past 2022.

    So what I need to alter the macro to do is:

    • Only copy across the Event and Impacts (and subsequently generate the trials etc) if the Event has an impact in that relevant Quarter (i.e Weather should only copy across to all worksheets starting with Q1 and Q4)
    • Only copy across the Event and impacts up until (and including) the year it impacts until. So Covid should only copy across to Q1Y22 and Q2Y22 worksheets as it no longer applies from 2023 onwards.

    Thanks for the feedback Roy, I've attached the workbook I'm using.


    Code is slightly different from above as I've semi-progressed (if you can call it) it to copy across the different worksheets. The macro is increasingly laggy with the current method though as the number of worksheets increases (currently 7 seconds for about 10 quarters or something, but this increased to 120 seconds for 20 quarters up to Q4Y2026).


    **EDIT** Any tips on how to actually get the excel workbook to upload? I'm getting an error 'The parameter "className" is missing or invalid.'

    I have way exceeded my excel knowledge and am struggling to comprehend how to progress a code to the next stage.

    My current set of code works for what it is required to do. I'm trying to develop a probabilistic model (P10/P50/P90) which runs quarterly for 5 years based on a set of inputs which are supplied on a single page. I recognise this is maybe fairly complex (at least for me!!) so I'll try to explain what I've done so far before explaining what I need to do next.

    The input sheet (OP Inputs) contains the following user input data:



    Event nameInput by the User in Columns A & B and combined by Excel formula in Column C
    Probability of each case (Low Case to High High case, some only have 3 cases, some have 4)Input to Columns D-G (Hidden columns H-K convert these into decimals)
    Consequence of each case (Matching the Low to High High) in terms of days lost revenueInput to Columns P-S (Hidden columns L-O convert these into quarterly figures since model is run quarterly not entire year)
    Random info (not relevant to macro)

    Columns T & U

    (See below for input sheet)


    So far, this is the only information I have used in my macro which is working (I'll get to the final columns in a bit). My macro code as included below achieves the following things:


    1. Standard turning off calculation while VBA works it's magic so as to not lag my poor laptop too much
    2. Counts number of rows in column C because this is relevant to subsequent transposing
    3. Transposes data from this source workbook, to the target workbook (which is TRead and currently represents Q1 of 2022 hence called Q1Y22). Data transposed includes
      1. Event title as per Column C
      2. The case probability from hidden columns H-K (requires the decimal format for a formula later on)
      3. The lost revenue days from hidden columns L-O
    4. It then loops back through everything and where there was a 'Title' row in the source worksheet (as you can see in the picture deemed Uncontrollables, Planned, Unplanned), it will delete these empty columns
    5. Worksheet adds the random trials for the probabilistic modelling (5000 random trials related to the probability - it will return the lost revenue days based on the randomly generated number)
    6. Finally adds some calculations in Columns A-F of the Target sheet, sorts these to allow excel to find the P10/P50/P90 probabilistic points and summarizes these in a table at the top of the worksheet

    See the output in a picture below the code:

    VBA Code:


    (current output)



    I'm honestly happy and impressed I got that far. Excuse the incomplete scribbles in the code at the bottom - that's me trying to work out this next piece.

    As you can see from the above screenshot, there are multiple tabs for every Quarter (Q1-Q4) for the next five years (until end 2026).

    What I need to do next is essentially what I've done, but for all other tabs and pending data on the input page in Columns W and X.

    Column W captures in which Quarters, the event has an impact. For some events, they only impact in certain quarters (i.e the weather line which only impacts operations in Q1 and Q4), but others may impact across all or sporadic quarters

    Column X captures when the impact is expected to end. For weather - there is no end. But for other issues (Covid for example), there's expected to be no ongoing inpact past 2022.

    So what my macro needs to do next is:


    • Only copy across the Event and Impacts (and subsequently generate the trials etc) if the Event has an impact in that relevant Quarter (i.e Weather should only copy across to all worksheets starting with Q1 and Q4)
    • Only copy across the Event and impacts up until (and including) the year it impacts until. So Covid should only copy across to Q1Y22 and Q2Y22 worksheets as it no longer applies from 2023 onwards.

    I'm struggling to figure out how to alter my code for this next step.