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:

ActiveSheet.Range("A19").FormulaArray = "=INDEX(References!m10:m24, MATCH(1, (State=References!A10:A24)*(Customer=References!B10:B24), 0), 0)"

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:

Dim wbInv as String

If iState = "AL" Then
    wbInv = "FileName1.xlsx"
End If
ActiveSheet.Range("D20") = Workbooks(wbInv).Worksheets("Chevron").Range("K16")

Doesn't Work:

Dim wbInv as String

If iState = "AL" Then
    wbInv = "FileName1.xlsx"
End If
ActiveSheet.Range("D15").Formula = "=wbInv"

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!

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.