2nd Post - Urgently need help on VBA Procedure

Hello,

I have the following hardcoded function in a worksheet:


=IF(ISNA(MATCH(1,('[Option 11 CSV.xls]May'!A1:A10000=20)*('[Option 11
CSV.xls]May'!B1:B10000=6)* ('[Option 11 CSV.xls]May'!C1:C10000="F")*('[Option
11 CSV.xls]May'!E1:E10000="Escada"),0)),0,INDEX('[Option 11
CSV.xls]May'!F1:F10000,MATCH(1,('[Option 11
CSV.xls]May'!A1:A10000=20)*('[Option 11 CSV.xls]May'!B1:B10000=6)*('[Option
11 CSV.xls]May'!C1:C10000="F")*('[Option 11
CSV.xls]May'!E1:E10000="Escada"),0)))


I urgently need to have this function as a VBA procedure, it needs to be
included in an existing VBA macro. Also, the name of file (Option 11
CSV.xls)wil change, so can we have the option of selecting the file to


--
Regards,
Jeff

see response in .misc

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have the following hardcoded function in a worksheet:
>
>
> =IF(ISNA(MATCH(1,('[Option 11 CSV.xls]May'!A1:A10000=20)*('[Option 11
> CSV.xls]May'!B1:B10000=6)* ('[Option 11
CSV.xls]May'!C1:C10000="F")*('[Option
> 11 CSV.xls]May'!E1:E10000="Escada"),0)),0,INDEX('[Option 11
> CSV.xls]May'!F1:F10000,MATCH(1,('[Option 11
> CSV.xls]May'!A1:A10000=20)*('[Option 11
CSV.xls]May'!B1:B10000=6)*('[Option
> 11 CSV.xls]May'!C1:C10000="F")*('[Option 11
> CSV.xls]May'!E1:E10000="Escada"),0)))
>
>
> I urgently need to have this function as a VBA procedure, it needs to be
> included in an existing VBA macro. Also, the name of file (Option 11
> CSV.xls)wil change, so can we have the option of selecting the file to
>
>
> --
> Regards,
> Jeff
>

can we assume the file will be Open

sName = "'[Option 11 CSV.xls]May'"
sform = "=IF(ISNA(MATCH(1,(XXX!A1:A10000=20)*(XXX!B1:B10000=6)*" & _
"(XXX!C1:C10000=""F"")*(XXX!E1:E10000=""Escada""),0)),0,INDEX(" & _
"XXX!F1:F10000,MATCH(1,(XXX!A1:A10000=20)*(XXX!B1:B10000=6)*" & _
"(XXX!C1:C10000=""F"")*(XXX!E1:E10000=""Escada""),0)))"
s1 = Replace(sForm,"XXX",sname)

Now s1 contains the formula as a string that can be evaluated or assigned to
the formula property of a cell.

res = Evaluate(s1)

or
ActiveCell.Formula = S1
--
regards,
Tom Ogilvy



"Jeff" wrote:

> Hello,
>
> I have the following hardcoded function in a worksheet:
>
>
> =IF(ISNA(MATCH(1,('[Option 11 CSV.xls]May'!A1:A10000=20)*('[Option 11
> CSV.xls]May'!B1:B10000=6)* ('[Option 11 CSV.xls]May'!C1:C10000="F")*('[Option
> 11 CSV.xls]May'!E1:E10000="Escada"),0)),0,INDEX('[Option 11
> CSV.xls]May'!F1:F10000,MATCH(1,('[Option 11
> CSV.xls]May'!A1:A10000=20)*('[Option 11 CSV.xls]May'!B1:B10000=6)*('[Option
> 11 CSV.xls]May'!C1:C10000="F")*('[Option 11
> CSV.xls]May'!E1:E10000="Escada"),0)))
>
>
> I urgently need to have this function as a VBA procedure, it needs to be
> included in an existing VBA macro. Also, the name of file (Option 11
> CSV.xls)wil change, so can we have the option of selecting the file to
>
>
> --
> Regards,
> Jeff
>