Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Track Real Time Changes In The Stock Market Additional Help Requested

    Hi Maude,

    I direct my question to you since you provided me with a really great solution!

    Is it possible to extend it to another sheet (SUMMARY) within the same workbook.

    So far only one Sheet ALL works with the comments tracking routine.

    Thank you very much and

    Best regards,

    Wolfgang
    Last edited by wolfgang; 2015-03-21 at 10:02. Reason: Sheet Description

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Wolfgang,

    Could you post a recent sample of the workbook and I will gladly make the changes? Or would you rather me use the copy from your previous thread?

    http://windowssecrets.com/forums/sho...l=1#post963591

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    thank you for your fast response!

    Yes, please use the copy because the workbook has grown over time and requires some passwords to get into the Internet tables.

    The layouts remained the same only sheet SUMMARY should also be tracked now, if you please.

    Wolfgang

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Maude, you should definitely charge for that service, even if it's only a beer!

    cheers, Paul

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    You are right,

    I suggest a Schneider Weisse Dunkel...

    Can not be beaten...

    Wolfgang

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Lol Paul! For a beer, I'll do just about anything.
    Schneider Weisse Dunkel
    Probably one of the few I never tasted

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Next time I'm in Germany.....

    cheers, Paul

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    WG,

    Attached is the original test workbook with an added connection for MSN Stocks/Money:
    http://www.msn.com/en-us/money/markets

    See the code below. Highlighted in blue are the 3 simple changes that you will need to make for each new connection in addition to creating your connection on the "Summary" sheet in this particular case.

    In the Class Module, Make the changes in blue:
    Code:
    Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
    '---------------------------------
    'DECLARE VARIABLES
    Dim cell As Range
    Dim rng As Range
    Dim rng1 As Range
    '---------------------------------
    'SET rng TO THE RANGE THAT YOU WANT THE VALUES TO MOVE TO COMMENTS
    Set rng = Worksheets("sheet1").Range("A1:A601") 'CHANGE RANGE
    Set rng1 = Worksheets("Summary").Range("A1:A468") 'CHANGE RANGE
    '---------------------------------
    'MOVE CELL VALUES TO COMMENTS
    For Each cell In rng
        cell.ClearComments
        If cell <> "" Then
            cell.AddComment
            If IsNumeric(cell) Then
                cell.Comment.Text Text:=Str(cell.Value)
            ElseIf IsDate(cell) Then
                cell.Comment.Text Text:=Str(cell.Value)
            Else:
                cell.Comment.Text Text:=cell.Value
            End If
        End If
    Next cell
    'MOVE CELL VALUES TO COMMENTS
    For Each cell In rng1
        cell.ClearComments
        If cell <> "" Then
            cell.AddComment
            If IsNumeric(cell) Then
                cell.Comment.Text Text:=Str(cell.Value)
            ElseIf IsDate(cell) Then
                cell.Comment.Text Text:=Str(cell.Value)
            Else:
                cell.Comment.Text Text:=cell.Value
            End If
        End If
    Next cell
    End Sub
    Make sure to change the range in the code for the new connection. Remember, if you interrupt the code you must reactivate it by either re-opening the workbook or re-running the InitializeQueries() in the standard module

    HTH,
    Maud
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    you spend your time even on a Sunday with Excel and funny requests.

    I generated a stripped-down version of my workbook and incorporated your code.

    When I do a REFRESH ALL I get an error message "Type Mismatch".

    I attach the workbook and no hurry, please.

    Best,
    Wolfgang
    Attached Files Attached Files

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    WG,

    Looking at the error, it was generated in cell J6 on the "All" sheet. Please see the image below.

    WG1.png

    Worksheet "All" cell J6 has the formula =I6/G6-1 however, since G6 is zero, the code is attempting to write a #DIV/0! error to a cell comment. The resolution is to adjust your formula depending on what the formula is supposed to do to either: =I6/(G6-1) OR =IF(G6=0,"",I6/G6-1) to avoid the #DIV/0! error. I made the adjustment in J6 and the code executed until it hit the same issue in J8, J12, J14, J16, and J18. Basically, any of those cells in col J that has a formula with a denominator in col G where the value = zero


    WG2.png

    HTH,
    Maud

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2015-03-23)

  12. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    great stuff, thank you very much for your time and help!

    Have a nice week.

    Best,
    Wolfgang

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    WG,

    Please get back to me if you are unable to resolve the issue and if the second connection doesn't work out.

    Maud

  14. #13
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    everything works just fine...

    Best,
    Wolfgang

Posting Permissions

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