Complex VBA code - Multiple IF conditions including Worksheet Name contains text partially matching Cell

  • 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.

  • 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.'

  • You can try saving as an xlsb file which might help.


    To upload a workbook then click the Attachments button in the rely box, then browse for the file. If it fails to upload try zipping it.

  • Yes - I’ve attached it to the last post. It’s the file called ‘Example.xlsb’. Hopefully that should be accessible for you?

  • 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.
  • 'm not sure if the code gives the correct results but I've removed the part that selects each sheet and it runs between 2 & 4 seconds.

  • 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
    1. 'Read the year something applies in from Column W and only copy the data for those years.
    2. 'Nest within this functionality, the ability to then read which quarter (Q1-Q4) the data should apply in and only copy to those quarters
    3. '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)
    4. ' If (Cell W right(2)) <= Right(.Name, 2) Then (do not copy)
    5. ' ElseIf (Cell W) = "N/A" Then (copy to all)
    6. ' Else (could maybe get rid of else if and just copy to all)
    7. 'End If