INDEX and MATCH with VBA

[msquared99]

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."


Else

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

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

Worksheets("Sheet1").Range("H3").Select
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

Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("A:C").Select
Columns("A:C").EntireColumn.AutoFit
Cells.Select
Selection.RowHeight = 12
Worksheets("Sheet1").Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-2]"
With Worksheets("Sheet1").Range("D3")
.AutoFill Destination:=Range("D3:D" & lastRow2)
End With

 

[sijpie]

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?

 

[msquared99]

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.

 

[sijpie]

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.

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

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

 

[msquared99]

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.

Thanks!

 

[sijpie]

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

 

[msquared99]

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.

 

[sijpie]

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))"

 

[msquared99]

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.

 

[sijpie]

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))"