[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Logging

Posted on 2011-10-28
35
Medium Priority
?
243 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:cpatte7372
  • 20
  • 15
35 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 37044720
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
 

Author Comment

by:cpatte7372
ID: 37046708
Badotz, thanks for replying.

Im a little confused as to how that can be achieved....
0
 
LVL 29

Expert Comment

by:Badotz
ID: 37046901
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:Badotz
ID: 37046905
//
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
 

Author Comment

by:cpatte7372
ID: 37047036
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37047462
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
 

Author Comment

by:cpatte7372
ID: 37047760
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37047785
um, to what does .value refer?
0
 

Author Comment

by:cpatte7372
ID: 37048230
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37050087
Stocks(i) is nowhere to be seen in your example code, so I'm not sure what you expect me to do?
0
 

Author Comment

by:cpatte7372
ID: 37053222
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
 

Author Comment

by:cpatte7372
ID: 37053224
Oops, forgot attachment
Price-Pause-v2.xlsm
0
 
LVL 29

Expert Comment

by:Badotz
ID: 37053242
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
 

Author Comment

by:cpatte7372
ID: 37053274
OK, I will do that now and let you know.

Cheers
0
 

Author Comment

by:cpatte7372
ID: 37053278
Badotz

The strange thing is .. it only occurs when I insert your code...
0
 

Author Comment

by:cpatte7372
ID: 37053288
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
 

Author Comment

by:cpatte7372
ID: 37053292
In the points again to Stocks(i) = .Value
0
 

Author Comment

by:cpatte7372
ID: 37053298
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
 
LVL 29

Accepted Solution

by:
Badotz earned 2000 total points
ID: 37053591
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
 

Author Comment

by:cpatte7372
ID: 37054193
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37054659
    ''
    '' =========================================================
    '' =========================================================
    '' 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
 

Author Comment

by:cpatte7372
ID: 37055530
Oh I see.... crap!
0
 
LVL 29

Expert Comment

by:Badotz
ID: 37055551
You might try using named ranges instead of hard-coded R1C1 addresses; use a little indirection...
0
 

Author Comment

by:cpatte7372
ID: 37055597
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
 

Author Comment

by:cpatte7372
ID: 37055603
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
 

Author Comment

by:cpatte7372
ID: 37055608
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
 

Author Comment

by:cpatte7372
ID: 37055626
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37055631
A named range refers to a cell or group of cells. You can then use the name for AQ3 instead of AQ3, etc.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 37055638
Or move the "log" to a separate worksheet so that updates do not interfere with "AQ3", etc.
0
 

Author Comment

by:cpatte7372
ID: 37055678
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37055727
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37055729
And

row * I suspect.

should be

row 8 I suspect.
0
 

Author Comment

by:cpatte7372
ID: 37055778
Badotz,

I figured it out. You've been great mate.... Can't thank you enough.
0
 

Author Closing Comment

by:cpatte7372
ID: 37055783
Thanks mate...
0
 
LVL 29

Expert Comment

by:Badotz
ID: 37055797
What did you do?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question