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

 

[nemmi69]

Not sure I have got it. Can you show a manual example of what you want to happen.

 

[offthelip]

try this code , you haven't said where you want the inputs and outputs so I assumed A1 and B1

Private Sub Worksheet_Change(ByVal Target As Range)
If lasttime = "" Then lasttime = 1


thistime = Cells(1, 1)
If thistime > 5 And lasttime <= 5 Then
 Application.EnableEvents = False
 Cells(1, 2) = Cells(1, 2) + 1
 Application.EnableEvents = True
End If
lasttime = thistime




End Sub

 

[subfuntres]

Hi Many thanks for answers.
Tried your vba code.
Th problem is as follows
I entered a value above 5 in cell A1 and B1 increases by one.
But as the value of a1 can increase up to fiflty evertime it changes it changes B1
I would like to value to change only once within that range of 5-50 and then change again when it falls below 5 and then increasea again above 5

 

[Stiuart_W]

Hi Many thanks for answers.
Tried your vba code.
Th problem is as follows
I entered a value above 5 in cell A1 and B1 increases by one.
But as the value of a1 can increase up to fiflty evertime it changes it changes B1
I would like to value to change only once within that range of 5-50 and then change again when it falls below 5 and then increasea again above 5

t would help if you wrote a example step by step with the expected output at each stage

 

[r1998]

Hello subfuntres
Data is from a1 to a20 and column b has the required output
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Sub Macro1()

Dim i As Integer, j As Integer
j = 0


For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row


If Cells(i, 1).Value > 5 Then
j = j + 1
End If


If Cells(i, 1).Value < 5 Then
j = 0
End If


If j = 1 Then
Cells(i, 2).Value = j
End If

Next i


End Sub

 

[offthelip]

I forgot to tell you to delare the variable "lasttime" as a global variable in a standard module

Global lasttime

This means that it is persistent between changes on the workhseet change event.
Sorry

 

[subfuntres]

Hi Again,

This is what I am looking for:

Cell a1 = 200
Cell a2 has input from a sensor ranging from 0-50
Each time a2 equals 5 or more then one is added to a1
But I need it only triggered between 5 and 50 for each event
The input of 5-50 represents depth of a submarine and each time it passes 5 mts it counts one more dive.
When the sub surfaces above 5 mts and then dives again below 5 mts it again counts one within the range of 0-50 mts

It is used to count the number of dives per day

 

[r1998]

Hello again

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

Dim j As Integer
j = 0
Set Target = Range("a2")
If Target.Value >= 5 Then
j = j + 1
End If

If Target.Value < 5 Then
j = 0
End If

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

End If
End Sub

 

[subfuntres]

Hello again,

Many thanks

Ran your code and yes it triggers when value reach above 5. But also triggers on any other value above 5.
It should only trigger once per cycle of 0-50 input