Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

Thread: Simple Copy and Update Cell Value Every Minute

  1. #1

    Simple Copy and Update Cell Value Every Minute

    To Whom It May Concern:

    I have the following issue.

    I have an Excel file with several sheets as tabs inside the file.
    The first tab of the Excel file is connected to stream live data feed.

    What I want is to take the values from a particular cell on that "live feed sheet" at 1-minute increments and populate these values one after another into cells in another tab of the same file.
    The value are updated continuously during the day.

    So, let's say the values in a live feed are located in A1 cell of Worksheet 1.
    I want values from this cell in 1-min increments to populate into cells A1, A3, A5, A7, A9... etc on Worksheet 2 of the same excel file.

    Also, in cells A4, A6, A8, A10, A12, etc... I need a formula where A4 = (A3-A1); A6 = (A5-A3); A8 = (A7-A5), and etc. Is it possible to create only one formula that calculates difference between preceding cell - (preceding cell - 2)?

    Could you please recommend what to do? I'm not an Excel expert and not a macro person.

    I have Microsoft Excel 2010, Student Edition.
    Last edited by Pastry_chef; 09-01-2018 at 07:17 PM.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Put this macro in a standard code-module:
    Sub blah()
    With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)    'destination sheet, bottom of column A
      .Value = Sheets("Sheet1").Range("A1").Value    'source sheet and cell
      .Offset(1).FormulaR1C1 = "=R[-1]C-R[-3]C"    'add a formula in the cell below
    End With
    If UCase(Sheets("Sheet2").Range("B1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
    End Sub
    Run it once to start the process.
    To stop the process enter the word stop in the cell specified in the last line of the macro. It will produce one more pair of values then stop.
    To restart the process, remove the word stop from the cell and run blah.
    To keep it simple, it starts at row 2, not row 1; you can live with deleting that cell at the top of column A later, any time after the first pair of values has arrived. The macro always adds information below existing data in column A.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    P45Cal, thank you very much for replying. Let me try this!
    I'm not VBA savvy, please give me a couple of hours :-)
    Thank you very much for your reply!!!

  4. #4
    Ok, I just realized that I gave the wrong cell count.
    Ok, here we go, here is what is needed.

    Live data is being populated on Sheet 1, cells: P11-P24 and X11-X24.
    Here is what I need (I attached a screenshot): in 1-min increments, I need data populated into Sheet 9 for the following cell schema (attached), it is better to see once, than to explain in multiple sentences.

    Another question I wanted to ask you: is it possible to also create a rule, where alert is issued whenever the value in columns "Difference" exceeds 2,000 (>=2,000) (an audio and a color alert).

    I would be happy to send you support via PayPal for this solution.

    Thank you!!!Screenshot.jpg

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Pastry_chef View Post
    Ok, I just realized that I gave the wrong cell count.
    Yeh, sure.
    Talk about moving the goal posts. You've changed their width, made 'em taller than they're wide, oh, and changed the name of the game to Tennis.
    Sub blah()
    With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14)    'destination sheet, to the right of data in row 3
      .Value = Sheets("Sheet1").Range("P11:P24").Value    'source sheet and range 1
      .Offset(, 1).Value = Sheets("Sheet1").Range("X11:X24").Value    'source sheet and range 2
      If .Column = 5 Then .Offset(, -1).ClearContents    'removes formulae from column D
      .Offset(, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
    End With
    If UCase(Sheets("Sheet9").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Pastry_chef View Post
    is it possible to also create a rule, where alert is issued whenever the value in columns "Difference" exceeds 2,000 (>=2,000) (an audio and a color alert).
    There are 14 values in those columns. Should they all exceed 2,000, one of them, some of them? to set off an alert.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Please don't be upset with me :-)
    I'm a very nice person and I'm very grateful for your help!!! This is sincere and truly!!!
    One more question.
    If I wanted to add more rows below, like attached. How do I expand your code, so that adjust it myself in the future without bothering you again? B/c I know you're busy.
    Please send me your PayPal link, I would be very happy to compensate you for your help. I'm not interested in milking your help for free!!!
    I'm truly very appreciative :-)Screenshot.jpg

  8. #8
    If any one cell (under column "Difference") exceeds >=2,000, then it sends alert.
    All of them, will Never get to that value simultaneously and a group will not get to that value there.
    It will be a cell, here and there.

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Your screenshot isn't quite high enough resolution to read properly. Can you provide a better one?
    I have little time today but… I'll answer both your questions when I can, probably tomorrow UK time.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    I have copied everything into a separate file, called it "Sheet 9".
    So, the data (in reality) would go from Sheet 1 to Sheet 9 of the SAME file.

    This "Sheet 9" that is attached is just a "dummy" excel file to show layout.
    Attached Files Attached Files

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In answer to msg#7:
    Sub blah()
    With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14)    'destination sheet, to the right of data in row 3
      .Value = Sheets("Sheet1").Range("P11:P24").Value    'source sheet and range 1
      .Offset(15).Value = Sheets("Sheet1").Range("AF11:AF24").Value    'source sheet and range 3
      .Offset(, 1).Value = Sheets("Sheet1").Range("X11:X24").Value    'source sheet and range 2
      .Offset(15, 1).Value = Sheets("Sheet1").Range("AD11:AD24").Value    'source sheet and range 4
      If .Column = 5 Then
        .Offset(, -1).ClearContents    'removes formulae from column D
        .Offset(15, -1).ClearContents    'removes formulae from column D
      End If
      .Offset(, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
      .Offset(15, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
    End With
    If UCase(Sheets("Sheet9").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
    End Sub
    msg#8 response coming presently…
    Last edited by p45cal; 09-05-2018 at 06:10 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    For sound:
    'Public Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long                  'needed for Beep with arguments.
    Sub blah()
    With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14)    'destination sheet, to the right of data in row 3
      .Value = Sheets("Sheet1").Range("P11:P24").Value    'source sheet and range 1
      .Offset(15).Value = Sheets("Sheet1").Range("AF11:AF24").Value    'source sheet and range 3
      .Offset(, 1).Value = Sheets("Sheet1").Range("X11:X24").Value    'source sheet and range 2
      .Offset(15, 1).Value = Sheets("Sheet1").Range("AD11:AD24").Value    'source sheet and range 4
      If .Column = 5 Then
        .Offset(, -1).ClearContents    'removes formulae from column D
        .Offset(15, -1).ClearContents    'removes formulae from column D
      End If
      .Offset(, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
      .Offset(15, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
      If .Column <> 2 Then
        If Application.Max(.Offset(, 2), .Offset(15, 2)) > 2000 Then
          For Each cll In Union(.Offset(, 2), .Offset(15, 2)).Cells
            If cll.Value > 2000 Then cll.Interior.Color = 255
          Next cll
          Application.ScreenUpdating = True
          'choose one or more of the following:
          Beep 'Beep (windows alert sound) without arguments requires the DISABLING/ABSENCE/REMOVAL of the Public Declare function at the top of the code module
    '      Beep 1200, 500    'Beep with arguments (frequency,duration) NEEDS the Public Declare function at the top of the code module'
    '      Beep 1200, 500    'Beep with arguments (frequency,duration) NEEDS the Public Declare function at the top of the code module'
          Application.Speech.Speak "at least one cell has a difference over 2000"
    
    
    
        End If
      End If
    End With
    If UCase(Sheets("Sheet9").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
    End Sub
    Note the comments in the code. The top Public Declare line is currently disabled by prefixing it with an apostrophe which reduces it to a comment.
    There are many more sound possibilities, see:
    https://www.ozgrid.com/forum/forum/h...ain-percentage
    https://www.ozgrid.com/forum/forum/h...t-pausing-code
    http://www.cpearson.com/excel/PlaySound.aspx
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    To stop the macro, do I enter stop in between <>?

  14. #14
    I get an error, so far.
    Runtime error 9. Subscript out of range.
    I'm asked to debug the follow code line:
    With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14) 'destination sheet, to the right of data in row 3

  15. #15
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Pastry_chef View Post
    To stop the macro, do I enter stop in between <>?
    No. Just stop, or Stop, or sToP, no spaces in cell A1 of Sheet9 (The sheet called Sheet9 on its tab, not the workbook called Sheet9.
    In Excel there are Workbooks (files), which contain (at least one) worksheets (sheets). Naming a workbook Sheet9 is more than a little confusing.


    Quote Originally Posted by Pastry_chef View Post
    I get an error, so far.
    Runtime error 9. Subscript out of range.
    I'm asked to debug the follow code line:
    With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14) 'destination sheet, to the right of data in row 3
    Do you have a sheet called Sheet9? If not, either rename the sheet Sheet9 or adjust the code so that references to Sheet9 in the code are changes to the actual sheet name.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  16. #16
    Thank you, very much!!!!!
    It works.
    I just had to change the name of the tabs without the space in between.
    Thank you very much for your help!!!!
    I really appreciate it!!!!!
    Please let me know how to thank you!!!!
    I really appreciate the time you invested in answering my question!!!!

  17. #17
    Ok, I forgot everything how we did this.
    I need something a bit different, but forgot how we did this originally, so I need to ask you again.

    Suppose, I have cells where the data changes every 30 seconds.
    I want to plot the current value from that cell, data point from 30 seconds ago, from 1 minute ago and 1.5 minutes ago.
    Basically, to have a stacked dynamic column.
    The yellow and purple columns are the current values. Data from 30-sec ago, 1 min ago and 1.5 min ago is shown in grey bars.
    Basically, these grey bars are the look-back bars to see how data changed over the last 1.5 minutes.

    Here is the image of what I want to do, b/c this shows it clearer.
    Attachment 26246

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    When I click on your attachment I get: "Invalid Attachment specified."
    Attaching an Excel workbook would be infinitely preferable.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    Let's do this for now: I have a cell (1 cell ) in Excel that updates values every 30 seconds or so (from a live feed).
    How do I plot the values from that cell in a chart vs. time (in 30 sec or 1 min increments on the x-axis).
    Is it possible to do?

  20. #20
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Sub blah()
    With ActiveSheet
      With .Cells(Rows.Count, "C").End(xlUp).Offset(1)
        .Value = .Range("A1").Value
        .Offset(, -1).Value = Now()
      End With
      .ChartObjects("Chart 1").Chart.SetSourceData Source:=Intersect(.Range("B:C").Resize(.Rows.Count - 8).Offset(1), .Range("B2").CurrentRegion)
      If UCase(.Range("B1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:00:30"), "blah"
    End With
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •