Link to home
Start Free TrialLog in
Avatar of cpatte7372
cpatte7372Flag for United Kingdom of Great Britain and Northern Ireland

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").Interior.ColorIndex = 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\ABC.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").Interior.ColorIndex = 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\ABC.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").Interior.ColorIndex = 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
Avatar of Badotz
Badotz
Flag of United States of America image

You would need two text files, one to write the latest value into (14), followed by the contents of the other (12,20,3,).
Avatar of cpatte7372

ASKER

Badotz, thanks for replying.

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
//
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
//

Open in new window

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
Change this
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog

Open in new window

to this
ThisWorkbook.Sheets("mini sized DOW").rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").value = xLog

Open in new window

Or something like that?
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?
um, to what does .value refer?
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
Stocks(i) is nowhere to be seen in your example code, so I'm not sure what you expect me to do?
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
Oops, forgot attachment
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.
OK, I will do that now and let you know.

Cheers
Badotz

The strange thing is .. it only occurs when I insert your code...
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
In the points again to Stocks(i) = .Value
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").Value >= 31 Or ActiveSheet.Range("AV3").Value >= 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\ABC.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
ASKER CERTIFIED SOLUTION
Avatar of Badotz
Badotz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
    ''
    '' =========================================================
    '' =========================================================
    '' 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")

Open in new window

Oh I see.... crap!
You might try using named ranges instead of hard-coded R1C1 addresses; use a little indirection...
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
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
Hi Badotz,

Not sure what you mean by 'You might try using named ranges instead of hard-coded R1C1 addresses; use a little indirection...'

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.

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.
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...
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.
And

row * I suspect.

should be

row 8 I suspect.
Badotz,

I figured it out. You've been great mate.... Can't thank you enough.
Thanks mate...
What did you do?