Board Regular
Mar 8, 2012
I have this code where the user can select a file. There will also be a file open and active called "Any AR"

How would I write this INDEX and MATCH formula in VBA?

ActiveCell.FormulaR1C1 = _
"=INDEX('[Any AR 03.01.13 PM.xls]Sheet1'!R3C4:R170C4,MATCH(RC[-2],'[Any AR 03.01.13 PM.xls]Sheet1'!R3C8:R170C8,0))"

Where Any AR 03.01.13 PM = FileName as referenced below and the active workbook is Any AR in the VBA code.

VBA Code Begins here:

Sub ChooseFile()

Dim fd As FileDialog
Dim FileName As String
Dim lastRow1 As Long
Dim lastRow2 As Long

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Get the number the button chosen.
Dim FileChosen As Integer

FileChosen = fd.Show

If FileChosen <> -1 Then

'Didn't choose anything (clicked cancel).
MsgBox "No file opened."


'Display name and path of file chosen.
FileName = fd.SelectedItems(1)
Workbooks.Open (FileName)

lastRow1 = Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-6]"
With Worksheets("Sheet1").Range("H3")
.AutoFill Destination:=Range("H3:H" & lastRow1)
End With

End If

Workbooks("Any AR.xls").Activate

lastRow2 = Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row - 1

Selection.Delete Shift:=xlUp
Selection.RowHeight = 12
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-2]"
With Worksheets("Sheet1").Range("D3")
.AutoFill Destination:=Range("D3:D" & lastRow2)
End With

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
When you write code in the forum please enclose in code markers:
[ code] your code [/ code]
(leave out the space before the c)

That makes it easy readable

Now it isn't exactly clear what you want. Where should this reference to your formula come, and why isn't it working what you have?
Upvote 0
I'm looking to put it at the end of the code above.

My issue is instead of "Any AR 03.01.13 PM.xls" I need that to be FileName in the formula.

When I try to use FileName I get errors. It must have something to do with where I'm putting " and '

Thanks for reading.
Upvote 0
Ah, be careful to use ' in code, as that signals a comment. So the compiler might complain. It should be OK within a string (within the " "), though.

Rich (BB code):
ActiveCell.FormulaR1C1 = _
    "=INDEX([FileName.xls]Sheet1!R3C4:R170C4," &  _ 
    "MATCH(RC[-2]," & _

I have broken up the formula in three parts above to make it more readable

Upvote 0
Thanks for taking the time to help me.

So how would the formula be written like the below example?

The code above worked.

[worksheets("sheet1"),range("E3").Formula =_
"=index([filename.xls]sheet1!("$D3:$D$" & lastrow1, " & "_
match("D4"," & "[filename.xls]sheet1!("$H$3:$H" & lastrow1),0))"]

I think I enclosed the code correct.

Upvote 0
Rich (BB code):
worksheets("sheet1").range("E3").Formula =_
"=index([filename.xls]sheet1!("$D3:$D$" & lastrow1, " & "_
match("D4"," & "[filename.xls]sheet1!("$H$3:$H" & lastrow1),0))"

no you didn't enclose the code correct: use [ code] your code comes here, the code between the square brackets is just that :the word 'code'. after your code you type [/ code] then it will appear nicely in a box

OK about your code: you had a comma in the first line where it should be a dot
Upvote 0
When I put the code in my macro it gives me a Complie Error Invalid Character on $ in $D$3:$D

I have tried playing with the code but also get a Run-time Error 1004.
Upvote 0
Yes you made a total mess of the ""....
worksheets("sheet1").range("E3").Formula =_
"=index([filename.xls]sheet1!($D3:$D$" & lastrow1 & "), " & _
"match(D4,[filename.xls]sheet1!($H$3:$H" & lastrow1 & "),0))"
Upvote 0
I copied the code and inserted it into my macro. I get a Run-time Error 1004

Could this be because of the way I'm getting "FileName"?

In post #1 look at how I get "FileName".

Does FileName maybe need to be Dim as a workbook?

I'm thinking maybe this has something to do with it.
Upvote 0
Ah. No your dim is OK. But the string isn't. You need to allow Basic to fill in the filename in the string. Now it thinks Filename is part of the string. So it should be:
worksheets("sheet1").range("E3").Formula =_
"=index([" & filename & ".xls]sheet1!($D3:$D$" & lastrow1 & "), " & _
"match(D4,[" & filename & ".xls]sheet1!($H$3:$H" & lastrow1 & "),0))"
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back