Index function in VBA - on Multiple cells

[Alan]

Struggling here and maybe being a bit thick, however had a look at Tom
Ogilvys resonse to a similar question on the 16 August 2006, tried to use the
same principals to no avail.

I have a number of PC's with large number of files that I am trying to tidy
up. I have created worksheets for all the files named on the IP Address. I
wish to have an index function that returns information dependant on 3 fields
in the main page.

I am trying to use Index and Match functions to obtain the information.
The code works fine with the formula in the cell, however with a large
number of PC's I would need to run the machine overnight !

The function I have tried is below

Function GetFileVersion(strFilePath As String, strFileName As String,
strFileType As String, strPCIP As String)

Dim ans As Variant

ans = Evaluate("INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((strFilePath &
strFileName & strFileType), ('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP &
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))")

If Not IsError(ans) Then
MsgBox ans
Else
MsgBox "Not found"
End If

GetFileVersion = ans

End Function

Any help or pointers would be greatly appreciated

Regards

 

[Bob Phillips]

Try this

Function GetFileVersion(strFilePath As String, _
strFileName As String, _
strFileType As String, _
strPCIP As String)
Dim ans As Variant
Dim sFormula As String

sFormula = "INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((""" & _
strFilePath & """&""" & strFileName & """&""" & strFileType
& _
"),('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP & _
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))"

ans = Evaluate(sFormula)

If Not IsError(ans) Then
GetFileVersion = ans
Else
GetFileVersion = "Not found"
End If

End Function



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan]

Bob

Again many thanks for your response ... however seems i get a negative
result although I know the file is there ...

The code within the cell is

=INDEX('###.##.#.###'!$K$1:$K$5000,MATCH(B7&C11&D11,'###.##.#.###'!$A$1:$A$5000&'###.##.#.###'!$B$1:$B$5000&'###.##.#.###'!$D$1:$D$5000,0))

And I get the right info

Any Ideas ???

Thanks

 

[Alan]

Bob

Cancel ... my typing is not what it shoulf be ... syntax and brackets
Once again very much obliged for your help

Regards