Posts by harbour1302

Re: VBA only fire on second entry in cell

I have also requested help here;

https://www.mrexcel.com/forum/…ing-code.html#post4854924

Re: VBA only fire on second entry in cell

The ranges that i require to offset are G8:G130, I8:I130, K8:K130

is this still possible if i change the range in your code to mirror my range in the existing code?

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?

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Static OldVal As Variant
    Dim r As Long, c As Long
    
    With Range("G8:K130")
        If Not IsEmpty(OldVal) Then
            For r = 1 To .Rows.Count
                For c = 1 To .Columns.Count Step 2
                    If .Cells(r, c) <> OldVal(r, c) Then .Cells(r, c + 1) = OldVal(r, c)
            Next c, r
        End If
        OldVal = .Value
    End With
    Application.EnableEvents = True
 End Sub

Thanks

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

:lol: recieved your reply just as I posted

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.

Thanks

Re: Type mismatch error

No this isn't supposed to happen and if I'm honest I don't know what this means :lol:

What could be causing the error?

Re: Type mismatch error

Ok,

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

Thanks

forum.ozgrid.com/index.php?attachment/72873/

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

Re: Edit code to include multiple ranges in worksheet_Calculate

Solved

Hi guys,

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

Private Sub Worksheet_Calculate()
     Static OldVal As Variant
     Dim rng As Range
     Set rng = Me.Range("A6")
     If IsEmpty(OldVal) Then OldVal = Range("A6").Value
     If Range("A6").Value <> OldVal Then
         With rng.Offset(, 1)
         .Value = OldVal
         End With
         OldVal = Me.Range("A6").Value
         End If
 End Sub

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 https://www.mrexcel.com/forum/excel-...ml#post4854148

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

You are a genius!

Thank you so much, so much simpler to just have the old value sheet calculate the result using the same formula!!

Re: Store oldvalue of selection_change and formula recalculation

forum.ozgrid.com/index.php?attachment/72822/

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

Really appreciate your time and expertise to this point though Carim, you are a legend!

I might have to admit defeat on this one :lol:

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

Hi Carim,

It doesnt appear to be copying the results only the previous entry from F4:I4

Is there an adjustment needed to the code to also copy the resuly values from F8:I130?

Thanks again

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

The only cells that are actually manually changed are F4:I4.

However i would need the results from F8:I130 to be archived in the "Old Values" sheet whenever a change occurs.

Thanks again dude

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