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
cpatte7372Asked:
Who is Participating?
 
BadotzConnect With a Mentor Commented:
No, but I avoid With..End With because it hides my intent:

Private Sub Worksheet_Calculate()
   
    Const cITEM as Integer = 3
    Static Stocks(cITEM) As Double      'Starts with element 0
    Dim cel As Range
    Dim Addr As Variant
    Dim targ As Variant
    Dim i As Long
    Dim 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
    ''
    '' =========================================================
    '' =========================================================
    '' 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")
   
    ''Look for prices above these threshhold values
    targ = Array(30, 30, 30, 30)
   
    For i = 0 To cITEM
        If Not IsError(Range(Addr(i)).Value) Then
            If Stocks(i) <> Range(Addr(i)).Value Then
                Stocks(i) = Range(Addr(i)).Value
               
                If Range(Addr(i)).Value > targ(i) Then
                    '' Change path & name to suit
                    Open "C:\Users\User\Documents\ABC.txt" For Append As #1
                   
                    Write #1, Range(Addr(i)).Address(False, False), Range(Addr(i)).Value, Date, Format(Time, "hh:mm:ss.ss")
                    Close 1
                End If
            End If
        End If
    Next
End Sub
0
 
BadotzCommented:
You would need two text files, one to write the latest value into (14), followed by the contents of the other (12,20,3,).
0
 
cpatte7372Author Commented:
Badotz, thanks for replying.

Im a little confused as to how that can be achieved....
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
BadotzCommented:
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
0
 
BadotzCommented:
//
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

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

Cheers
0
 
cpatte7372Author Commented:
Badotz

The strange thing is .. it only occurs when I insert your code...
0
 
cpatte7372Author Commented:
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
0
 
cpatte7372Author Commented:
In the points again to Stocks(i) = .Value
0
 
cpatte7372Author Commented:
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
0
 
cpatte7372Author Commented:
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?
0
 
BadotzCommented:
    ''
    '' =========================================================
    '' =========================================================
    '' 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

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

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

0
 
cpatte7372Author Commented:
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.

0
 
BadotzCommented:
A named range refers to a cell or group of cells. You can then use the name for AQ3 instead of AQ3, etc.
0
 
BadotzCommented:
Or move the "log" to a separate worksheet so that updates do not interfere with "AQ3", etc.
0
 
cpatte7372Author Commented:
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...
0
 
BadotzCommented:
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.
0
 
BadotzCommented:
And

row * I suspect.

should be

row 8 I suspect.
0
 
cpatte7372Author Commented:
Badotz,

I figured it out. You've been great mate.... Can't thank you enough.
0
 
cpatte7372Author Commented:
Thanks mate...
0
 
BadotzCommented:
What did you do?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.