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:
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.
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)