Sub to Convert a Dynamic Range of Text with Blanks to a Number from a Closed Workbook RRS feed

  • Question

  • Hello

    I would like to carry out a simple Index/Match between an open and closed workbook with multiple sheets.  I want to use Index/Match rather than a VLookup as the columns on the Lookup_Sheet could be in any order. The Conversion sheet has multiple columns of data. 

    Dim Conversion_Sheet As Workbook 'The sheet with the data I am trying to convert and will be the Activesheet

    Dim Static_Codes As Workbook 'The workbook with the static codes

    Dim Lookup_Sheet As Worksheet 'The sheet I want to look up the data from

    The range is dynamic and is based on Column A and may be 10 one day and 10000 the next day. 

    The data that I want to look up and convert on the Conversion_Sheet can be in any column with the look up result appearing in the column beside it say Column D and E.  The data I want to convert in Column D will contain blanks.

    So if I had GBP in cell D2 of the Conversion_Sheet the VBA would open the closed Static_Codes Workbook and go to the Currency Lookup_Sheet find GBP in Column B and return 134 to Column E2 on the Conversion_Sheet from Column C on the Lookup_Sheet.  (Column B and C on the Lookup_Sheet will also change and could be lets say Column D and F)  The Static_Codes Workbook would then be closed with no changes.

    Other Conversion_Sheets will require two or three columns to be looked up however if I can get the Sub to carry out the above conversion for one column I will be able to duplicate it as necessary for multiple lookups (I think).

    I have searched for days on forums and help pages however am having no luck finding what I am looking for so any help would be a great help.

    I am happy to send over some sample data if required as I am not totally sure the above all makes sense.

    Thanks in Advance for your Help.


    Wednesday, March 22, 2017 7:31 AM

All replies

  • Without knowing more about your data it sounds like you should use ADODB to connect to your lookup sheets.  You would use ordinary SQL to select the information.  You may also be able to use ADODB to connect to the other sheets and update the info.  Overall, it sounds like you should look at using a database rather than using Excel as a database.  Excel is not very reliable as a database.
    Wednesday, March 22, 2017 11:30 AM
  • Thanks for the advice however i need to find an excel solution. Is there a way I can attach or send sample data? Thanks Again Dougie
    Wednesday, March 22, 2017 1:18 PM
  • ADODB is an Excel solution.  Just reference Microsoft Active Data Objects Library.  Probably v6.1.  I've delivered addins using it to 1000's of people with basic Office and have never had a single problem.

    It supports very complex SQL including subqueries and table joins.  It also supports numerous functions like Instr and Datedif.  It is surprisingly fast since it actually reads the data directly from disk.  If it can work in your application you'd be crazy not to use it.

    • Edited by mogulman52 Wednesday, March 22, 2017 4:44 PM
    Wednesday, March 22, 2017 4:35 PM
  • Thanks again Mogulman however I cant use Access as a database as not all of my team have that program and it will cause problems.

    I have various solutions to calculate the range.

    If you could help with the Index/Match code from a closed workbook with the data being converted having blanks that would be great.



    • Edited by Dougie79 Wednesday, March 22, 2017 10:21 PM
    Wednesday, March 22, 2017 10:15 PM
  • You don't need Access to use Microsoft Active Data Objects Library.  If you install any version of Office you have Microsoft Active Data Objects Library and can use ADODB.  Microsoft Active Data Objects Library is much better and way more capable than Index/Match.
    Thursday, March 23, 2017 1:12 AM