+ Reply to Thread
Results 1 to 7 of 7

How to shorter my macro module ?

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    How to shorter my macro module ?

    Dear all, I have recorded three macro module for retrieve web data & it works very well, this web data contains stock high & low price ,similarly I need to analysis more about 200 stock for my personal portfolio loss gain calculation. So if I want to retrieve 200 stock , then I need to build up 200 macro record module , which is not wise decision. for this I need to implement looping vba concept as some parameter are same for all Stock , below is the recorded macro module for only three stock.
    Please Login or Register  to view this content.
    here red color mark text is the individual stock name & green color text is the excel cell number where web data will be past onwards. however i will be change
    just URL link & desire cell range, i need your assistance regarding this , take care every body.. nur

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,220

    Re: How to shorter my macro module ?

    Hi nur,

    This isn't perfect but can give you an idea of how to loop and use an array and index to shorten your code:
    You should also look at the VBA "Split()" function to shorten some of the array stuff. You can leave out the quote marks and make it a little simpler.

    Please Login or Register  to view this content.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Re: How to shorter my macro module ?

    Hi MarvinP,
    Thanks for reply, Array solution is best , But i was not sure the limitation of Array where I have 200 stock. I thought my stock List will be remain at one sheet (like sheet name “stock”) , & every time macro will take one after one stock & run the macro as well I assumed this the looping concept ! . what ever it is I just want how minimum time it takes to retrieve data form from web to excel .little bit compiler error fixed by below.

    End With
    Next StockCtr
    End Sub
    Last edited by nur2544; 06-13-2013 at 11:53 PM.

  4. #4
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Re: How to shorter my macro module ?

    Hi MarvinP, now it works fine but , the web data past column A, then Column B, likewise Column C,D,E,F,G,H,I,.... till last stock. but i need the stock price in the column A only. where 6 cell interval there will be new stock price , other than it will be overlapping one stock with other stock.
    CELL A1 for stock ABBANK
    CELL A7 for stock NBL
    CELL A13 for stock EBL
    CELL A19 ..
    CELL A25 ..
    CELL A31 ..
    CELL A37 ..
    .. ..
    .. ..
    Till last 200 stock.
    Last edited by nur2544; 06-14-2013 at 12:12 AM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,220

    Re: How to shorter my macro module ?

    Hi,

    I really need a sample of what you have and what you want to modify my code much. You have two sheets? The first has all stock symbols on it in Col A? You want to build Sheet 2? AND you need it to report back on each 6th row? I simply need a sample. OR Better YET!!!!! Upgrade to 2013 Excel and look at:
    http://www.stockodo.com/stock-quotes...h-office-2013/
    http://www.free-power-point-template...or-excel-2013/

  6. #6
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Re: How to shorter my macro module ?

    Hi MarvinP, yes you are right & your got my point , please check the attachment, where sheet "web" is my desire output results. and sheet "stocklist" is the all stock list from here need to data retrieve for all listed stock.
    Attached Files Attached Files
    Last edited by nur2544; 06-14-2013 at 01:11 AM.

  7. #7
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Re: How to shorter my macro module ?

    Hi MarvinP, How to increase array limitations in VBA , i want to keep 302 stock list in the array , already you have given me an example where i can data retrieve for three stock.
    Please Login or Register  to view this content.
    i have tried to keep all 302 stock in the array but showing error.need your assistance regarding my issue.

    instead of above code can i write like below ?
    Please Login or Register  to view this content.
    Last edited by nur2544; 06-14-2013 at 11:55 AM.

+ Reply to Thread

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