cpatte7372
asked on
Excel Logging
Hello Experts,
An Expert helped me with the following formula. The logging of the data goes from top to bottom with the latest value at the bottom . I was wondering if it would be possible to have the most recent value appearing at the top:
so instead of
12
14
With 14 being the most recent value, I would like the data logged so that 14 would be at the top:
14
12
Sub StartTimer()
Dim xLog As String
DoEvents
If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
If myCount > 15 Then
'set lastAlert =2
If lastAlert <> 2 Then
lastAlert = 2
End If
DoEvents
ElseIf myCount = 15 Then
'Call tenminalert(True)
'Call shiftDown
'-- green highlite when over 15 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interio r.ColorInd ex = 4
xLog = Format(Now(), "hh:MM:ss") & " Highlight 15 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim x() As String
x = Split(xLog, ":")
Select Case x(1)
Case "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "51", "59"
Beep
End Select
Open "C:\Users\User\Documents\A BC.txt" For Append As #1 'Change path & name to suit
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576") .End(xlUp) .Offset(1, 0).Value = xLog
ElseIf myCount > 10 Then
'set lastAlert =1
If lastAlert <> 1 Then
lastAlert = 1
End If
DoEvents
ElseIf myCount = 10 Then
'Call fiveminalert(True)
'Call shiftDown
'-- yellow highlite when over 10 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interio r.ColorInd ex = 6
xLog = Format(Now(), "hh:MM:ss") & " Highlight 10 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
x = Split(xLog, ":")
Select Case x(1)
Case "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "51", "59"
Beep
End Select
Open "C:\Users\User\Documents\A BC.txt" For Append As #1 'Change path & name to suit
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576") .End(xlUp) .Offset(1, 0).Value = xLog
End If
myCount = myCount + 1
Else
myCount = 1
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interio r.ColorInd ex = xlNone
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub
Cheers
Carlton
An Expert helped me with the following formula. The logging of the data goes from top to bottom with the latest value at the bottom . I was wondering if it would be possible to have the most recent value appearing at the top:
so instead of
12
14
With 14 being the most recent value, I would like the data logged so that 14 would be at the top:
14
12
Sub StartTimer()
Dim xLog As String
DoEvents
If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
If myCount > 15 Then
'set lastAlert =2
If lastAlert <> 2 Then
lastAlert = 2
End If
DoEvents
ElseIf myCount = 15 Then
'Call tenminalert(True)
'Call shiftDown
'-- green highlite when over 15 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interio
xLog = Format(Now(), "hh:MM:ss") & " Highlight 15 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim x() As String
x = Split(xLog, ":")
Select Case x(1)
Case "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "51", "59"
Beep
End Select
Open "C:\Users\User\Documents\A
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576")
ElseIf myCount > 10 Then
'set lastAlert =1
If lastAlert <> 1 Then
lastAlert = 1
End If
DoEvents
ElseIf myCount = 10 Then
'Call fiveminalert(True)
'Call shiftDown
'-- yellow highlite when over 10 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interio
xLog = Format(Now(), "hh:MM:ss") & " Highlight 10 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
x = Split(xLog, ":")
Select Case x(1)
Case "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "51", "59"
Beep
End Select
Open "C:\Users\User\Documents\A
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576")
End If
myCount = myCount + 1
Else
myCount = 1
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interio
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub
Cheers
Carlton
You would need two text files, one to write the latest value into (14), followed by the contents of the other (12,20,3,).
ASKER
Badotz, thanks for replying.
Im a little confused as to how that can be achieved....
Im a little confused as to how that can be achieved....
Write the first value (20) into "Output.txt", the main file.
Here's where it gets messy. For subsequent values:
Copy "Output.txt" to "File_A.txt"
Write the current value into "Output.txt", replacing all prior content.
Append the contents of "File_A.txt" to "Output.txt"
Delete "File_A.txt"
Repeat ad nauseum
Here's where it gets messy. For subsequent values:
Copy "Output.txt" to "File_A.txt"
Write the current value into "Output.txt", replacing all prior content.
Append the contents of "File_A.txt" to "Output.txt"
Delete "File_A.txt"
Repeat ad nauseum
//
File_A.txt Output.txt
========== ==========
-- 20
20 14,20
14,20 42,14, 20
42,14,20 37,42,14,20
37,42,14,20 99,37,42,14,20
//
ASKER
Badotz,
Thanks again for replying.
I didn't make myself clear. I'm actually happy with the way the output appears in the log files. I was referring to the way the logs appear on the actual spreadsheet.
Please see attached for illustration
EE-Price.xlsm
Thanks again for replying.
I didn't make myself clear. I'm actually happy with the way the output appears in the log files. I was referring to the way the logs appear on the actual spreadsheet.
Please see attached for illustration
EE-Price.xlsm
Change this
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog
to this
ThisWorkbook.Sheets("mini sized DOW").rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").value = xLog
Or something like that?
ASKER
Hi Badotz,
I inserted the code, however I get an error box stating:
Run-time error 13; Type mismatch. And then it points to Stocks(i) = .Value
Any thoughts?
I inserted the code, however I get an error box stating:
Run-time error 13; Type mismatch. And then it points to Stocks(i) = .Value
Any thoughts?
um, to what does .value refer?
ASKER
Hi Badotz,
Not quite sure.
When I open the spreadsheet I get the original error message:
"Can't Operate in Break Mode" and then it highlights: Stocks(i) = .Value
Not quite sure.
When I open the spreadsheet I get the original error message:
"Can't Operate in Break Mode" and then it highlights: Stocks(i) = .Value
Stocks(i) is nowhere to be seen in your example code, so I'm not sure what you expect me to do?
ASKER
Hi Badotz, sorry for not responding sooner - Halloween....
My bad, I forgot the attachment spreadsheet which where the error highlights Stocks(i).
So, when your formula is activated I get run-time error 13, Type mismatch and it highlights Stocks(i) = .Value
Cheers
My bad, I forgot the attachment spreadsheet which where the error highlights Stocks(i).
So, when your formula is activated I get run-time error 13, Type mismatch and it highlights Stocks(i) = .Value
Cheers
ASKER
Oops, forgot attachment
Price-Pause-v2.xlsm
Price-Pause-v2.xlsm
Without examining your code, you must be using a "With...End With" block. Step through the code with a debugger and determine what .Value is associated with; what the value if i is; if Stocks has the same variable type.
ASKER
OK, I will do that now and let you know.
Cheers
Cheers
ASKER
Badotz
The strange thing is .. it only occurs when I insert your code...
The strange thing is .. it only occurs when I insert your code...
ASKER
Badotz,
I should also mention that when I open the spreadsheet I now get the error message 'Can't execute in break mode'
Any idea what that means?
Cheers
I should also mention that when I open the spreadsheet I now get the error message 'Can't execute in break mode'
Any idea what that means?
Cheers
ASKER
In the points again to Stocks(i) = .Value
ASKER
Badotz,
This is code that gives me the mismatch.
Do I have too many End With's?
Private Sub Worksheet_Calculate()
Dim cel As Range
Dim Addr As Variant, targ As Variant
Static Stocks(3) As Double 'Starts with element 0
Dim i As Long, n As Long
'Added code
If ActiveSheet.Range("AQ3").V alue >= 31 Or ActiveSheet.Range("AV3").V alue >= 31 Then
Call soundAlert(True)
Else
Call soundAlert(False)
End If
Addr = Array("AQ3", "AV3", "AP8", "AO8") 'Watch these cells for price changes
targ = Array(30, 30, 30, 30) 'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
With Range(Addr(i))
If Not IsError(.Value) Then
If Stocks(i) <> .Value Then
Stocks(i) = .Value
If .Value > targ(i) Then
Open "C:\Users\User\Documents\A BC.txt" For Append As #1 'Change path & name to suit
Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
Close 1
End If
End If
End If
End With
Next
End Sub
Cheers
This is code that gives me the mismatch.
Do I have too many End With's?
Private Sub Worksheet_Calculate()
Dim cel As Range
Dim Addr As Variant, targ As Variant
Static Stocks(3) As Double 'Starts with element 0
Dim i As Long, n As Long
'Added code
If ActiveSheet.Range("AQ3").V
Call soundAlert(True)
Else
Call soundAlert(False)
End If
Addr = Array("AQ3", "AV3", "AP8", "AO8") 'Watch these cells for price changes
targ = Array(30, 30, 30, 30) 'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
With Range(Addr(i))
If Not IsError(.Value) Then
If Stocks(i) <> .Value Then
Stocks(i) = .Value
If .Value > targ(i) Then
Open "C:\Users\User\Documents\A
Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
Close 1
End If
End If
End If
End With
Next
End Sub
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Badotz,
I inserted your code above, but I'm still getting type mismatch, but this time it highights:
Stocks(i) = Range(Addr(i)).Value
Any thoughts why?
I inserted your code above, but I'm still getting type mismatch, but this time it highights:
Stocks(i) = Range(Addr(i)).Value
Any thoughts why?
''
'' =========================================================
'' =========================================================
'' If you INSERT a row above rows 3 or 8, then the following
'' Ranges become INVALID - "AQ3" should then be "AQ4", etc.
'' =========================================================
'' =========================================================
'' Watch these cells for price changes
Addr = Array("AQ3", "AV3", "AP8", "AO8")
ASKER
Oh I see.... crap!
You might try using named ranges instead of hard-coded R1C1 addresses; use a little indirection...
ASKER
OK, Badotz, I think I have a solution.
At the moment all events are being logged to the screen on the spreadsheet. Do you think you could show me how to modify a the code so that it will only log when an event occurs and overrides the existing event when a new event occurs? If you can do that, I will re-activate the 'call shiftdown' code in the spreadsheet.
Cheers
At the moment all events are being logged to the screen on the spreadsheet. Do you think you could show me how to modify a the code so that it will only log when an event occurs and overrides the existing event when a new event occurs? If you can do that, I will re-activate the 'call shiftdown' code in the spreadsheet.
Cheers
ASKER
Of course, unless there is a way to get you're code to prevent an insertion of rows above 3 and 8 (which would be better solution)
Cheers
Cheers
ASKER
Hi Badotz,
Not sure what you mean by 'You might try using named ranges instead of hard-coded R1C1 addresses; use a little indirection...'
Not sure what you mean by 'You might try using named ranges instead of hard-coded R1C1 addresses; use a little indirection...'
ASKER
Badotz,
I think if I could just get the code to present the results just once on the screen instead of each time an event occurs I think I might be able to solve it.
I think if I could just get the code to present the results just once on the screen instead of each time an event occurs I think I might be able to solve it.
A named range refers to a cell or group of cells. You can then use the name for AQ3 instead of AQ3, etc.
Or move the "log" to a separate worksheet so that updates do not interfere with "AQ3", etc.
ASKER
Hi Badotz,
'Or move the "log" to a separate worksheet so that updates do not interfere with "AQ3"',
That was how it compiled for my originally, however because I'm using the numbers to trade alongside the other variables you see on the spreadsheet it needs to be on the same sheet.
If you could show me how to just get the results appear just once I think I could take it from there.
Cheers mate...
'Or move the "log" to a separate worksheet so that updates do not interfere with "AQ3"',
That was how it compiled for my originally, however because I'm using the numbers to trade alongside the other variables you see on the spreadsheet it needs to be on the same sheet.
If you could show me how to just get the results appear just once I think I could take it from there.
Cheers mate...
Then move it below the last fixed-cell row - row * I suspect.
If you move the log to a separate worksheet, all of the problems with mucking up your main sheet disappear. You can refer to certain cells in the new sheet on your main sheet.
Generally speaking, separating data onto it's own worksheet makes it much easier to maintain your application sheet; this would be my approach.
If you move the log to a separate worksheet, all of the problems with mucking up your main sheet disappear. You can refer to certain cells in the new sheet on your main sheet.
Generally speaking, separating data onto it's own worksheet makes it much easier to maintain your application sheet; this would be my approach.
And
row * I suspect.
should be
row 8 I suspect.
row * I suspect.
should be
row 8 I suspect.
ASKER
Badotz,
I figured it out. You've been great mate.... Can't thank you enough.
I figured it out. You've been great mate.... Can't thank you enough.
ASKER
Thanks mate...
What did you do?