+ Reply to Thread
Results 1 to 4 of 4

Strings Variables in Range("A1").Formula = "=index/match" ?

  1. #1
    Registered User
    Join Date
    11-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    Strings Variables in Range("A1").Formula = "=index/match" ?

    I have just started working with VBA to try and automate some billing processes for my company. One of the steps I am trying to accomplish is to perform an Index/Match function on a table in a separate workbook. I am able to open the workbook and find values within the sheets but I am having some other problems, two namely

    One of them is that I can't figure out how to write an Index/Match function that uses only VBA code. My current solution to this is to make Range("A1").Formula = "=iindex/match calculations" as if I was typing these directly into the cell. This works just fine until I need to substitute one of the Index/Match ranges with a variable that is a string. The strings I am using work fine when using just VBA code but not when I am trying to put them into a cell. Here are some examples of the code that I am using that works and doesn't work:

    Works:
    Please Login or Register  to view this content.
    This returns the matched State and Customer value for the column of M10:M24 in the "references" tab. State and Customer are dependent lists that I have created in the excel sheet. This piece of code is self contained within one workbook.


    The next few examples are part of a larger set of code I am using to open a separate workbook to then pull from. I have written some snippets of code that work and some that don't, all of them posted below. I would like to repeat the index/match functionality that I have above while referencing the other book.

    Works:

    Please Login or Register  to view this content.
    Doesn't Work:
    Please Login or Register  to view this content.
    This last one I have simplified to the point of JUST trying to get the string (wbInv) to be put into the cell D15. Eventually I would like to use this wbInv string as a larger part of an index/match function. I realize that I could write it as Range("D15") = wbInv and have it output the string, but that isn't the final functionality I am looking for. I have searched all day trying to figure this out and finally decided to post.

    I hope this makes sense. basically, I would like to be able to figure out how to put variables that have been defined as strings into cells using .Formula. Or I would appreciate a walk through on how to turn my first Index/Match function (that would go directly into a cell) rewritten as a VBA code (without the .Formula part)

    Thanks!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Strings Variables in Range("A1").Formula = "=index/match" ?

    Why can't you use Range("D15") = wbInv?

    That will put the string in the cell.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Strings Variables in Range("A1").Formula = "=index/match" ?

    Quote Originally Posted by nadnerb5
    Eventually I would like to use this wbInv string as a larger part of an index/match function. I realize that I could write it as Range("D15") = wbInv and have it output the string, but that isn't the final functionality I am looking for.
    I touched on this in my post but I should clarify. I am trying to use variables from within the user drop down menus to find the workbook, worksheet and eventually cell within the separate workbook. That means I need to be able to put the string into the cell as part of a formula because the way I have it now, the string variable holds the name of the workbook.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Strings Variables in Range("A1").Formula = "=index/match" ?

    What exactly do you want to do with wbinv?

    You seem to want to use it in formula, can you post an example?

+ 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