Posts by mhabib

  • VBA Historical Price Data Loop

    Re: VBA Historical Price Data Loop


    JP,


    Sorry to let you down, I normally use VBA as a last resort since Excel has so many powerful features built into its immediate environment - plus VBA is really not my strong point.


    So attached is yet another formula-based solution. I've used the OFFSET/SUMPRODUCT combination to identify the correct computational ranges - with a couple of dynamic ranges thrown in for improved formula readability.


    I'm hopeful that this will satisfy your needs vis-a-vis AVG, STDEV, auto-filter etc. etc. But this is really the edge of my creativity.


    HTH


    m

  • VBA Historical Price Data Loop

    Re: VBA Historical Price Data Loop


    Hi JP,


    If you're willing to settle for a slightly different reporting format, then here's a suggestion that doesn't require VBA:


    1. In column (D), you could simply place an analyst code (or generate dummy entries via formulas- see attached workbook).


    2. Run up a Pivot table, with Security Names and Years as row fields, and Analyst codes as column fields


    3. Forecast EPS should be dropped as data items (with Average i/o Sum function)


    See attached workbook for details.


    HTH


    m

  • Copying data from the Internet

    Re: Copying data from the Internet


    Norie's suggestion sounds pretty painless - deleting blanks should take < 30 secs.


    Quote


    Can you not just goto Edit>Goto...Special... and select blanks then delete?


    You could even set it up as a macro to further speed it up.


    BTW, how are you parsing the data after downloading?


    m

  • checking appearance of a number, and telling the col num

    Re: checking appearance of a number, and telling the col num


    Yeah! I see it now. Dunno why I got the whacky message earlier.


    I'm a bit puzzled seeing your columns going right to left i/o left to right. However, this looks like a VBA problem to me - not my strong point. Hope one of the gurus here will solve this for you. Good Luck!


    m

  • Copying data from the Internet

    Re: Copying data from the Internet


    Hi Richard,


    Instead of using the Paste command, try using PasteSpecial > Values (or Text). It works for me when I download large amounts of financial stats (S&P 500 cos) from various websites.


    HTH


    m

  • Return Row reference for each cell in selection

    Re: Return Row reference for each cell in selection


    Hi Neiluk66,


    Without knowing the exact nature of your problem, all I can suggest is the following code, which allows you to cycle through each cell in your selection, and shows the address (you could do other stuff with it too).


    Code
    Sub Each_Cell_In_Selection()
    Selection.SpecialCells(xlCellTypeVisible).Select
    For Each a In Selection
        MsgBox a.Address
    Next
End Sub


    HTH


    m

  • In Column F make all dates 'X' days passed into Italic font

    Re: In Column F make all dates 'X' days passed into Italic font


    Hi DJC,


    The cause of the error is that one or more cells in column F contain "non-date" data. Try this modified code.


    Code
    Sub Past7Days() 
    Dim RngCell As Range 
    On Error Resume Next
    For Each RngCell In Range("F1", Range("F65536").End(xlUp)) 
        If Date - RngCell >= 7 Then 
            RngCell.Font.Italic = True 
        End If 
    Next 
End Sub


    HTH


    m

  • Append data (values) from one sheet to another - and apply formats

    Hi,


    I have a macro in Sheet1 that selects data and appends values to Sheet2


    Here's my code:


    Code
    Private Sub CommandButton1_Click()
    If IsEmpty(Range("A2")) Then Exit Sub
    Range("A2", ActiveCell.SpecialCells(xlCellTypeLastCell)).Copy
    Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0) _
        .PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A2", ActiveCell.SpecialCells(xlCellTypeLastCell)) _
        .ClearContents
End Sub


    The formatting in both sheets is different (Sheet2 contains conditional formatting). Therefore, I cannot simply bring in formats from Sheet1 into Sheet2.


    I need some code that will apply the formats from the last row of the data in Sheet2 (prior to append) to the newly appended data.


    Thanks.


    m

  • Cell's Value vs Text Property

    Hi,


    What's the difference between using a cell's .Value property or .Text property? Results of both these properties appear to be identical. For example, I entered the number 100 in cell A1 and ran the following code:


    Code
    Sub ValueVsText()
    MsgBox Sqr(Range("a1").Value)
    MsgBox Sqr(Range("a1").Text)
End Sub


    The end-result in both cases was 10 (the square root of 100). So why do we have two properties when one would do just fine?


    I'd appreciate an explanation in layman terms. What I'd appreciate even more are examples of situations when using one property is more beneficial than using the other.


    Thanks.


    m

  • sequence formula to infinity

    Re: sequence formula to infinity


    I may be over-simplifying here but how about this scheme?


    1. Write in the first seven days - say in cells A1 thru A7
    2. In cell A8, write the formula to refer to A1 (=A1)
    3. The formula in A8 may be copied down as many times as desired (within a total count of 65536, of course.)


    HTH


    m