VBA INDEX and MATCH

[msquared99]

I get invalid character message in the formula where I have $D$3:$D$ and $H$3:$H$.


Any thoughts?

[pre]

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

[/pre]

 

[Hui]

Msquared99


Lastrow is probably a Number so, Convert it to a String and remove any excess spaces


Try a small mod:

[pre]

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

or 

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

[/pre]

 

[shrivallabha]

Double quotes in the formula also seem to misplaced.

[pre]

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

[/pre]

 

[msquared99]

First of all thamks for the help.


Hui,


I tried both your suggestions and get a Compile Error Invalid Character on the $ in $D$3:$D


shrivallabha I get a Run-Time Error 1004 with yours.


I have lastRow1 as Long in the top half of the macro.


This code somewhat works but when you look at the formula in the spreadsheet it is referring to the workbook in the folder instead of the open workbook:

Worksheets("Sheet1").Range("E3").Formula = "=INDEX('[" & FileName & "]Sheet1'!$D$3:$D$10000,MATCH(D3,'[" & FileName & "]Sheet1'!$h$3:$h$10000,0))"

I don;t believe I've had a fromual quite so difficult!

 

[shrivallabha]

What does the formula look like when normally entered in Excel ( not in VBA)?


Will filename be the only variable or are you going to use lastrow1 as you've stated in the previous post?


The code posted will error out with mentioned error if the references to workbook / worksheets are wrong. Did you edit them to suit?

 

[msquared99]

If I type the formula in myself it should look like this:

=INDEX('[Any AR 03.01.13 PM.xls]Sheet1'!($D$3:$D170,MATCH(D4,'[Any AR 03.01.13 PM.xls]Sheet1'!($H$3:$H170,0))


But VBA writes it like this:

=INDEX('[C:Temp[Any AR 03.01.13 PM.xls]Sheet1]Any AR 03.01.13 PM.xls]Sheet1'!$D$3:$D170,MATCH(D4,'[C:Temp[Any AR 03.01.13 PM.xls]Sheet1]Any AR 03.01.13 PM.xls]Sheet1'!$H$3:$H170,0))


When I play around with the formual I get either a Compile Error Invalid Character or a Run-time Error 1004.


Any thoughts?

 

[shrivallabha]

How are you getting Filename? Do not use Filename as it clashes with built-in property. Use sFilename instead.


If you are using something like:

sFileName = Activeworkbook.FullName

Then use

[code]sFileName = Activeworkbook.Name

See if below works:

[pre]sFilename = "Any AR 03.01.13 PM.xls"
Worksheets("Sheet1&#34.Range("E3&#34.Formula "=INDEX('[" & sFilename & _
"]Sheet1'!($D$3:$D170,MATCH(D4,'[" & sFilename & "]Sheet1'!($H$3:$H170,0))"[/code][/pre]

 

[msquared99]

I'm getting it like this:

[pre]

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)

[/pre]

I hope I referenced the code right.

 

[shrivallabha]

Nicely done. See the last line I have added to get the FileName we need.

[pre]

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)
FileName = Mid(FileName, InStrRev(FileName, "") + 1, Len(FileName))

[/pre]

 

[msquared99]

IT WORKED! IT WORKED!!!!!


THANK YOU SOOOOO MUCH FOR HELPING ME!


You were very patient. Thank YOU!!!

 

[Debraj]

Nice Detective work.. Shri.. Congrats..

 

[shrivallabha]

msquared99, glad to know that you have a working solution.


Deb, thanks man.