Results 1 to 6 of 6

Thread: Record cell values into columns - Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    3

    Record cell values into columns - Excel

    Hello!

    English isn't my first language so I hope you will understand me


    I need help from Excel pros


    I have a workbook with two sheets ( datafeed and record )

    On "datafeed", column B, cells B2 to B300 I have live prices from internet


    Currently I use the following :

    Sub my_onTime()
    Application.OnTime Now + TimeValue("00:00:01"), "my_Procedure"
    End Sub

    Sub my_Procedure()
    With Sheets("record")
    rw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    .Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a3:b3").Value
    End With
    ThisWorkbook.RefreshAll
    my_onTime
    End Sub
    which records prices from"datafeed" to rows in "record", but that isnt what i want.


    My wish is :

    that Excel would record price changes to"record" Sheet into columns not rows.



    "Graphic" display what I want:


    Sheet1 - "datafeed" ... B1 = 155............Sheet 2 - "record".......cell B6 = 155.....cell C6 = 150.....cell D6 - 145.....cell E6 - 140,....


    Sheet1 - "datafeed" ... C1 = 66.............Sheet 2 - "record".......cell B6 = 66.....cell C6 = 67.....cell D6 - 66.....cell E6 - 65,....



    Also last recorded price must be put in first column Sheet 2 - "record" - in to cells: C6, D6, E6,......

    (Right now last recorded value is put in last row )


    Any help will be greatly appreciated.

    Thank you!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Record cell values into columns - Excel

    post a workbook with some sample data (zip first)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    3

    Re: Record cell values into columns - Excel

    Here is sample workbook

    https://files.fm/u/9cyd4fhu#_


    Here are 3 pics for better clarification of my prevous post of what I am hoping to achieve

    http://shrani.si/f/3N/kg/3QGLeDF9/datafeed.jpg

    http://shrani.si/f/3N/kg/3QGLeDF9/datafeed.jpg

    http://shrani.si/f/1o/L6/DATEXEG/wish.jpg

    @westconn1 Thank you!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Record cell values into columns - Excel

    you should attach all to your post here, rather than external sites

    you can test if this does what you want
    Code:
    Dim r As Worksheet, df As Worksheet, cel As Range, fnd As Range
    Set r = Sheets("record")
    Set df = Sheets("datafeed")
    With r
        For Each cel In df.Cells(2, 1).Resize(df.Cells(Rows.Count, 1).End(xlUp).Row - 1)
            Set fnd = .Range("d:d").Find(cel)
            If fnd Is Nothing Then
                Set fnd = .Cells(Rows.Count, 4).End(xlUp).Offset(1)
                fnd.Value = cel
                Else
                fnd.Offset(, 1).Insert xlToRight
            End If
            fnd.Offset(, 1) = cel.Offset(, 1)
        Next
    End With
    it appears to work correctly, if i understood right, and runs without error,
    any codes that are not found are added at the bottom of the column
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    3

    Re: Record cell values into columns - Excel

    @westconn1

    It works just like I wanted, so thank you very much Sir

    But now I have another problem:

    I would like to calculate % difference between current price and price from X seconds ago.

    For example; I entered the formula with the prices in column E and I, but when the workbook refreshed and dragged the formula with it. It did this:



    What needs to be done that formula will stay put and calculate % diference?

    Also, can you please rewrite the code, so the data on Record sheet would start with column W instead of D?

    Thank you!
    Attached Images Attached Images  

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Record cell values into columns - Excel

    Also, can you please rewrite the code, so the data on Record sheet would start with column W instead of D?
    Set fnd = .Range("d:d").Find(cel)
    change this line to W:W

    i am not really sure what is happening with the formula, or what you want result you want
    which columns do you want to compare?
    do you want to compare the same columns by number or the relocated columns after updating the worksheet?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width