vba code

[subfuntres]

I have a depth sensor with an output from 0-60
I would like to add a value of 1 to another cell each time the value exceeds 5, but only once.
when the value falls below 5 the cell does not change value and waits for the value to increase above the 5 threshold.
I have tried a vba code which adds a value when increased above 5 but also adds for every other value above 5 and when it falls below 5 it reverts back to previous number.
Hope I have explained this properly.

Any advice please

 

[r1998]

Hello again
sorry , but I am not able to understand your problem Please try to describe in more detail
i selected b1 with left mouse click, then pressed ctrl + c , then selected a2 with mouse and pressed ctrl + v , value of a1 changes

 

[subfuntres]

Ill will try and explain my problem

Cell a1 =200
Cell a2 is the cell with the value.
I put in a value of over 5 and cell a1 increases by 1 as it should do with the vba code.
eg I have now put a value in H10 =10
I copy H10 and paste link this value to A2
A1 now changes
Using H10 as the input value when I change H10 because of the paste link A2 also changes to this value, BUT a1 will now not change

 

[r1998]

Hello subfuntres
I finally figured out what the problem was
i did not know about the paste link feature of excel ( sorry for that )
n after knowing about it, i realized that paste link was causing this problem n i am not sure how to deal with this paste link issue
Instead of paste link, just enter the data in h10, it will be shown in a2 too, n will do the required calculation for a1
for that use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("h10")) Is Nothing Then


Set Target = Range("h10")
Range("a2").Value = Target


If Target.Value >= 5 And Target.Value <= 50 Then
Range("a10").Value = Range("a10").Value + 1
End If


If Target.Value < 5 Then
Range("a10").Value = ""
End If


If Range("a10").Value = 1 Then
Range("a1").Value = Range("a1").Value + 1
End If


End If


End Sub

 

[subfuntres]

Hi Thanks again for the help.
Unfortunately it will not work as our input H10 which we use to test at the moment, will actually be from a 0-50 sensor that is constantly varying from 0-50. That is why I used paste link

Will keep investigating and try and find the solution