Posts by harbour1302

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: VBA only fire on second entry in cell

    I never thought of that yes that could work!

    How would i implement that into this code?


    I have a strange question tbh I'm not even sure if it is possible.

    Is there a way to add code into a worksheet_calculate event that stops the code firing on the first entry and only the second.

    I have a range that has an old value change that offsets the old value by one column and I have a reset macro that clears the old value.

    My issue is I have another sheet that has the difference between the new value and old value i.e. A1-B1 however because the old value is always zero after first entry the difference is always just the new value.

    Ideally I would like the code to only fire if the values are changed after the first entry is made.

    Again I don't even know if this is possible :lol:

    Re: Type mismatch error

    I have found the issue.

    This is the only range with an MROUND formula because this item is ordered in multiples of 5 and because column F contains a minus value this is causing a #NUM error.

    I'll have to resort to using ROUNDUP instead.


    Re: Type mismatch error


    in (F4) Mon, Tues & Wed I am inputting 5.7
    in (G4) Thurs & Fri I am inputting 7.5
    Once I get to (I4) Sat, Sun & Mon and input 11.5 this is when the error occurs.

    these three cells plus (K4) Tues & Wed are updated with forecast multiple times in the week so I need the old value to be stored in offset (, 1) but I believe this is where the error is happening as when I hover over the code it says cell = error 2036

    I hope this makes sense


    Hi Guys,

    Can anyone help with my sheet?

    I am having type mismatch error when changing the forecast in cells F4, G4, I4 & K4.

    I input the formula and this creates a stock order based on a usage sheet.

    I also have a oldval code that offsets the values by 1 column when the sheet is adjusted.

    It seemed to be working fine yesterday.

    Any help would be awesome.

    Thanks guys

    Hi guys,

    i have a vba code for storing the old value of a formula result.

    I need to extend the range to G8:G130, I8:I130, K8:K130

    the idea being that the old value will offset by 1 column when it detects a value change.

    I have also asked another forum for support

    however i have had most success from here in the past!

    can anyone help?

    Thanks in advance

    Re: Store oldvalue of selection_change and formula recalculation

    I have found this sheet that tracks all cell changes is there a way to adjust the code to work on my sheet, instead of storing all values historically just overwriting in the target cell.

    and obviously include changes as a result of a formula result change istead of manual change.

    Re: Store oldvalue of selection_change and formula recalculation

    yes so each column in G8:I8 references a delivery day Wednesday:Monday

    What i've experinced is if you chnage all the values to 0 in F4:I4 to reset the old value and then change G4 to '50' the old value remains 0 but if i then change H4 to 50 also it copies the data that the G4 originally changed to old values even though no change has occured in the cells since the original input in G4.

    I would only need the old value to be stored if any the individual cell had changed. for example if i change the value in G4 and as a result G8 changes i would only need to save an old value if G8 chnaged again as a result of a formual recalculation.

    Is this making any sense

    Re: Store oldvalue of selection_change and formula recalculation

    Hi Carim,

    I feel like i'm being a nuisance now :lol:

    i have encountered an issue. I haven't got a clue how to fix.

    if i update the value in F4 to say 20 it updates the 'order calculator' with new value and the 'old value stays at 0. If i then update G4 to 20 it copies the new values from the F4 column also to the old data.

    so essentially the old data becomes inaccurate as it copies every value when F4:I4 is changed rather than only values that have changed.

    I'm confusing myself now :?

    I'm assuming then the code would need to include a worksheet change for all values including result changes and track each cell individually for changes and only copy the oldvalue if a change has occured in that cell etc.
    There has to be an easier way surely.

    Re: Store oldvalue of selection_change and formula recalculation

    Yes that is to correct,

    and have the 'Old Values' update with the previous cell value from the 'order calculator' every time a change occurs.

    Maintaining the previous value on one sheet and the new value on another, with the end goal being to have a third sheet that contains the differnces between the old value and new value.

    If that is possible that is :roll:

    Re: Store oldvalue of selection_change and formula recalculation

    Carim, this is amazing.

    It is so close to what i require.

    Is there a way to copy the old value to the old value sheet whilst updating the active sheet with new values.

    Eventually on the sheet ammeded orders i would like a formula the has the difference between the old value and new value to assist with ammending previously submitted orders.

    at the moment the code copies the values upon a worksheet_change in F4:I4

    but is there a way to introduce a variable that stores the previous cell value on the "old value" sheet, while updating the "Order Calculator" with the new values.

    i hope i'm making sense :tongue:

    And again thank you so much for your help so far