how to preserve data?
 

hi all,

I'm having some problems in implementing the following. I hava a spreadsheet
that gets live data and I need to compare that with some other cells of mine
based on which I've developed a function that alerts me by showing a msgbox.
Thus suppose the liva data falls below a certain manually entered value, i
get a pop up.

the problem i am facing is that if my live data remains below the manual
value, i keep getting pop ups which I dont want. I need the alert just once
when it falls below that value. I tried using another column as a flag but
when i try to modify a new cell, it doesnt seem to work properly and my
function outputs aren't what they should be. I'm not too sure even if the
logic i'm using is correct. i'm relatively new to vba and excel so if anyone
could help, it would be great. Thanks!!

Abhay

how to preserve data?
 

Writing a flag to another cell should work, but then you would also need to
clear it at some point (when the item goes above the mark).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target.Value < Range("A1") Then
If IsEmpty(Target.Offset(0, 1)) Then
MsgBox "Run Run, the market is falling"
Target.Offset(0, 1).Value = 1
End If
ElseIf Not IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1).ClearContents
End If
End If
End Sub

I assume you are familiar with Events and the Change Event in particular.
If not look at Chip Pearson's page on events

http://www.cpearson.com/excel/events.htm

--
regards,
Tom Ogilvy



"Abhay Sanan" wrote in message
...
hi all,

I'm having some problems in implementing the following. I hava a
spreadsheet
that gets live data and I need to compare that with some other cells of
mine
based on which I've developed a function that alerts me by showing a
msgbox.
Thus suppose the liva data falls below a certain manually entered value, i
get a pop up.

the problem i am facing is that if my live data remains below the manual
value, i keep getting pop ups which I dont want. I need the alert just
once
when it falls below that value. I tried using another column as a flag but
when i try to modify a new cell, it doesnt seem to work properly and my
function outputs aren't what they should be. I'm not too sure even if the
logic i'm using is correct. i'm relatively new to vba and excel so if
anyone
could help, it would be great. Thanks!!

Abhay

how to preserve data?
 

Hey Tom,

thanks for the advice. But I think my problem is a little more complicated.
Basically, I have one column showing me live data and 2 other columns I need
to compare it with. And my spreadsheet is so crowded using offset(0,1) would
just not work out. I had created a function and not a Sub to show the msg
box and my code is as below.
================================================== =======================
Function check(cts As Integer, sstop As Double, lstop As Double, data As
Double, _
symbol1 As String, description1 As String, val As Integer, sto As Integer)
As VbMsgBoxResult

Dim posit As Integer
Dim slstop As Double
Dim bloom As Double
Dim symb1 As String
Dim desc1 As String
Dim cnt As Integer

cnt = val
bloom = data
posit = cts
symb1 = symbol1
desc1 = description1

If posit = 0 Then
check = ""
Exit Function
End If

' cnt is a column that goes to 1 or 0 dependin on if my value has fallen
below or has risen back above the given mark.
'cnt goes 1 automatically in my spreadsheet when the value in bloom falls
below or above my other 2 columns I'm comparing it with.
'posit or cts are my contracts I have on a trade. So when my contracts are
positive i.e. 1, i need to compare it
'with long stop. or else i need to compare it with short stop.
' what i tried to do was to use something like Range("DR11").value = 1 after
the msgbox line. But that doesnt seem to work.

If cnt = 1 Then

If (posit 0) Then
slstop = lstop

If bloom < slstop Then
check = MsgBox(Trim(symb1) & " & " & Trim(desc1), vbOKOnly, "WARNING")

End If

Else
slstop = sstop

If bloom slstop Then

check = MsgBox(Trim(symb1) & " & " & Trim(desc1), vbOKOnly, "WARNING")
End If

End If

End If

End Function
================================================== ========================

Any suggestions on what I can do now. I think I'm making my function too
complicated but I dont know how else to do it.
There is any empty column somewhere at the end of my spreadsheet where each
cell calls this function with the various parameters
passed through it....Thanks again in advance!!

Abhay

how to preserve data?
 

Besides using 2 or 3 variables where ever you only need one, your function
is pretty simple. If you want to condition your logic on past experience,
you can look in help at static variables.

--
Regards,
Tom Ogilvy

"Abhay Sanan" wrote in message
...
Hey Tom,

thanks for the advice. But I think my problem is a little more
complicated.
Basically, I have one column showing me live data and 2 other columns I
need
to compare it with. And my spreadsheet is so crowded using offset(0,1)
would
just not work out. I had created a function and not a Sub to show the msg
box and my code is as below.
================================================== =======================
Function check(cts As Integer, sstop As Double, lstop As Double, data As
Double, _
symbol1 As String, description1 As String, val As Integer, sto As Integer)
As VbMsgBoxResult

Dim posit As Integer
Dim slstop As Double
Dim bloom As Double
Dim symb1 As String
Dim desc1 As String
Dim cnt As Integer

cnt = val
bloom = data
posit = cts
symb1 = symbol1
desc1 = description1

If posit = 0 Then
check = ""
Exit Function
End If

' cnt is a column that goes to 1 or 0 dependin on if my value has fallen
below or has risen back above the given mark.
'cnt goes 1 automatically in my spreadsheet when the value in bloom falls
below or above my other 2 columns I'm comparing it with.
'posit or cts are my contracts I have on a trade. So when my contracts are
positive i.e. 1, i need to compare it
'with long stop. or else i need to compare it with short stop.
' what i tried to do was to use something like Range("DR11").value = 1
after
the msgbox line. But that doesnt seem to work.

If cnt = 1 Then

If (posit 0) Then
slstop = lstop

If bloom < slstop Then
check = MsgBox(Trim(symb1) & " & " & Trim(desc1), vbOKOnly, "WARNING")

End If

Else
slstop = sstop

If bloom slstop Then

check = MsgBox(Trim(symb1) & " & " & Trim(desc1), vbOKOnly, "WARNING")
End If

End If

End If

End Function
================================================== ========================

Any suggestions on what I can do now. I think I'm making my function too
complicated but I dont know how else to do it.
There is any empty column somewhere at the end of my spreadsheet where
each
cell calls this function with the various parameters
passed through it....Thanks again in advance!!

Abhay