Results 1 to 5 of 5

Code is placing data in the wrong range

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-16-2011
    Location
    Portland, OR
    MS-Off Ver
    Excel 2016 Preview
    Posts
    105

    Code is placing data in the wrong range

    The other parts of this code are running ok. This section is weird.

    The goal:
    Retrieve the stock information from the internet and import it into the Web query Page.
    Grab the range D5:I5 from the Web Query Page
    Place that data in range F:K on the Stock Symbols page (Starting on row 2 and lopping through until Column A is empty).

    What's actually happening:
    The import happens
    Range G5:L5 on the Web Query Page is selected
    Info is placed in F:K in the Stock Symbols page
    Right destination, wrong information

    NOTE: if this code looks weird, it's because I found existing code and have been massaging it to try to make it work for my purposes.



    '   Step 5 : Set Data Ranges for Quotes
        Set rngLookUpSym = Worksheets("Stock Symbols").Range("A2")
        Set rngQuerySym = Worksheets("Web Query Page").Range("A1")
        Set rngQuerySymCo = Worksheets("Web Query Page").Range("D5")
        Set rngQuerySymData = Worksheets("Web Query Page").Range("D5").Range("D1:I1")
        Set qryTableStocks = ThisWorkbook.Worksheets("Web Query Page").QueryTables(1)
    
    '   Step 6 : Loop through list of mutual fund symbols and retrieve Quotes
        Do While rngLookUpSym <> ""
            rngQuerySym = rngLookUpSym
        With qryTableStocks
            .Connection = _
            "URL;http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=" & rngQuerySym
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingAll
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With
            rngLookUpSym.Range("F1") = rngQuerySymCo
            'rngQuerySymData.Copy Destination:=rngLookUpSym.Range("F1")
            rngLookUpSym.Range("F1:K1") = rngQuerySymData.Value
            Set rngLookUpSym = rngLookUpSym.Offset(1, 0)
        Loop
    
    
    
    End Sub
    Last edited by Habanero Time; 01-24-2012 at 09:03 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1