+ Reply to Thread
Results 1 to 6 of 6

Excel 2003 - VBA to substitute value from cell in formula

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Excel 2003 - VBA to substitute value from cell in formula

    I am trying to work out if it is possible to implement the following to save me from performing a routine manually.

    History

    I have a spreadsheet that i use to perform somes checks against data held on Sharepoint. The item number is found using the following formula

    ='http://intranet/commercial/Product Vault/[item1_AS.XLS]Sheet1'!$C$12

    My question is, is it possible to have vba substitute the 'item1' part of the formula for a value held in a particular cell. as an example, cell A1 contains the following : Testing so when the VBA runs, it finds the value that is contained within cell A1, and then in cell B1 inputs the following formula

    ='http://intranet/commercial/Product Vault/[Testing_AS.XLS]Sheet1'!$C$12

    This would then allow me to amend the list in the column A and have the VBA return the values as required.

    there are some other minor changes that need to be made to the formula depending on the information I need to return, but whilst trying to pick up VBA i am trying to keep my questions to a minimum, in order to learn more for myself.

    The above seems to have me stumped though.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,115

    Re: Excel 2003 - VBA to substitute value from cell in formula

    You don't really need VBA for that, you could use INDIRECT.

    =INDIRECT("'http://intranet/commercial/Product Vault/[" & A1 & "_AS.XLS]Sheet1'!$C$12")


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-12-2011
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Excel 2003 - VBA to substitute value from cell in formula

    Thanks for the quick response. I shall input the formula and hopefully all is well.

    Am unfamiliar with the INDIRECT function so i have learnt something extra today

  4. #4
    Registered User
    Join Date
    01-12-2011
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Excel 2003 - VBA to substitute value from cell in formula

    Strange, it keeps throwing up a #ref error.

    is there a formatting or punctuation mark i may need to add to get this to work

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,115

    Re: Excel 2003 - VBA to substitute value from cell in formula

    Put a working formula into cell B1. Then put whatever comes after the square brackets in cell A1. Replace whatever you put in cell A1 with " & A1 & ". Put =INDIRECT(" at the beginning and ") the end. With a bit of luck the working formula will still work. Then you can change the value in A1 with another valid location and it should still work.

    Regards, TMS

  6. #6
    Registered User
    Join Date
    01-12-2011
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Excel 2003 - VBA to substitute value from cell in formula

    Just wondering if you were aware of any reasons that using the method described would throw an error if it was being asked to look at a file on SharePoint.

    It keeps returning the error that it was unable to download the information. However, when i input the formula the old way, its returns the value required.

    If not, i shall put it down as a 'cant be done task' and continue to input it manually.

    However, thanks for bringing the INDIRECT function to my attention as it looks like a helpful function

+ 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