2nd Posting - Need Help on VBA Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
You'll get a quicker response if you post this to the programming group.

microsoft.public.excel.programming
 
Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
 
Hi Bob,
I have the following msg for mtchValue = Error 2015.

--
Regards,
Jeff



Bob Phillips said:
Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

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

Jeff said:
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
Click to expand...
Click to expand...
 
Try this version instead Jeff

Function GetValue(filename)
Dim sh As String
Dim iPos As Long
Dim cell As Range
Const LastRow As Long = 20 '10000

iPos = InStrRev(filename, "\")
If iPos = 0 Then
sh = "'[""" & filename
Else
sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)
End If
sh = sh & "]Sheet1'!"
Set cell = ActiveSheet.UsedRange.Offset(1, 1)
cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _
"(" & sh & "R1C1:R" & LastRow & "C1=20)*" & _
"(" & sh & "R1C2:R" & LastRow & "C2=6)*" & _
"(" & sh & "R1C3:R" & LastRow & "C3=""F"")*" &
_
"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _
",0))"
GetValue = cell.Value
cell.Clear

End Function


--
HTH

Bob Phillips

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

Jeff said:
Hi Bob,
I have the following msg for mtchValue = Error 2015.

--
Regards,
Jeff



Bob Phillips said:
Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

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

Jeff said:
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
Click to expand...
Click to expand...
Click to expand...
 
Bob,
Thank for you help.
I have the following error msg:
Run-Time Error 1004
"Unable to set FormulaArray Property of the class Range"
--
Regards,
Jeff



Bob Phillips said:
Try this version instead Jeff

Function GetValue(filename)
Dim sh As String
Dim iPos As Long
Dim cell As Range
Const LastRow As Long = 20 '10000

iPos = InStrRev(filename, "\")
If iPos = 0 Then
sh = "'[""" & filename
Else
sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)
End If
sh = sh & "]Sheet1'!"
Set cell = ActiveSheet.UsedRange.Offset(1, 1)
cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _
"(" & sh & "R1C1:R" & LastRow & "C1=20)*" & _
"(" & sh & "R1C2:R" & LastRow & "C2=6)*" & _
"(" & sh & "R1C3:R" & LastRow & "C3=""F"")*" &
_
"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _
",0))"
GetValue = cell.Value
cell.Clear

End Function


--
HTH

Bob Phillips

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

Jeff said:
Hi Bob,
I have the following msg for mtchValue = Error 2015.

--
Regards,
Jeff



Bob Phillips said:
Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

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

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
Click to expand...
Click to expand...
Click to expand...
 
Did you correct the wrap-around?

--
HTH

Bob Phillips

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

Jeff said:
Bob,
Thank for you help.
I have the following error msg:
Run-Time Error 1004
"Unable to set FormulaArray Property of the class Range"
--
Regards,
Jeff



Bob Phillips said:
Try this version instead Jeff

Function GetValue(filename)
Dim sh As String
Dim iPos As Long
Dim cell As Range
Const LastRow As Long = 20 '10000

iPos = InStrRev(filename, "\")
If iPos = 0 Then
sh = "'[""" & filename
Else
sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)
End If
sh = sh & "]Sheet1'!"
Set cell = ActiveSheet.UsedRange.Offset(1, 1)
cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _
"(" & sh & "R1C1:R" & LastRow & "C1=20)*" & _
"(" & sh & "R1C2:R" & LastRow & "C2=6)*" & _
"(" & sh & "R1C3:R" & LastRow & "C3=""F"")*" &
_
"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _
",0))"
GetValue = cell.Value
cell.Clear

End Function


--
HTH

Bob Phillips

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

Jeff said:
Hi Bob,
I have the following msg for mtchValue = Error 2015.

--
Regards,
Jeff



:

Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

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

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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure understand the "wrap-around"
--
Regards,
Jeff



Bob Phillips said:
Did you correct the wrap-around?

--
HTH

Bob Phillips

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

Jeff said:
Bob,
Thank for you help.
I have the following error msg:
Run-Time Error 1004
"Unable to set FormulaArray Property of the class Range"
--
Regards,
Jeff



Bob Phillips said:
Try this version instead Jeff

Function GetValue(filename)
Dim sh As String
Dim iPos As Long
Dim cell As Range
Const LastRow As Long = 20 '10000

iPos = InStrRev(filename, "\")
If iPos = 0 Then
sh = "'[""" & filename
Else
sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)
End If
sh = sh & "]Sheet1'!"
Set cell = ActiveSheet.UsedRange.Offset(1, 1)
cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _
"(" & sh & "R1C1:R" & LastRow & "C1=20)*" & _
"(" & sh & "R1C2:R" & LastRow & "C2=6)*" & _
"(" & sh & "R1C3:R" & LastRow & "C3=""F"")*" &
_
"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _
",0))"
GetValue = cell.Value
cell.Clear

End Function


--
HTH

Bob Phillips

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

Hi Bob,
I have the following msg for mtchValue = Error 2015.

--
Regards,
Jeff



:

Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

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

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
Click to expand...
Click to expand...
Click to expand...
 
Well, for instance, this in the code

sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)

should all be on one line, as should

cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _

and

"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _

--
HTH

Bob Phillips

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

Jeff said:
I'm not sure understand the "wrap-around"
--
Regards,
Jeff



Bob Phillips said:
Did you correct the wrap-around?

--
HTH

Bob Phillips

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

Jeff said:
Bob,
Thank for you help.
I have the following error msg:
Run-Time Error 1004
"Unable to set FormulaArray Property of the class Range"
--
Regards,
Jeff



:

Try this version instead Jeff

Function GetValue(filename)
Dim sh As String
Dim iPos As Long
Dim cell As Range
Const LastRow As Long = 20 '10000

iPos = InStrRev(filename, "\")
If iPos = 0 Then
sh = "'[""" & filename
Else
sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)
End If
sh = sh & "]Sheet1'!"
Set cell = ActiveSheet.UsedRange.Offset(1, 1)
cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _
"(" & sh & "R1C1:R" & LastRow &
Click to expand...
"C1=20)*"
& _
"(" & sh & "R1C2:R" & LastRow &
Click to expand...
"C2=6)*" &
_
"(" & sh & "R1C3:R" & LastRow & "C3=""F"")*" &
_
"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _
",0))"
GetValue = cell.Value
cell.Clear

End Function


--
HTH

Bob Phillips

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

Hi Bob,
I have the following msg for mtchValue = Error 2015.

--
Regards,
Jeff



:

Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh &
Click to expand...
"F1:F10000,"
&
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

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

Hello,

I have the following hardcoded function in a worksheet:


=IF(ISNA(MATCH(1,('[Option 11
Click to expand...
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
Click to expand...
needs
to
be
included in an existing VBA macro. Also, the name of file
Click to expand...
(Option
11
CSV.xls)wil change, so can we have the option of selecting the file to
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2nd Post - Urgently need Help on VBA Procedure 1
Error 2015 2
Error 2015 with Application.Evaluate 3
formula to display cell address 2
Help with SUMPRODUCT 4
Formula needed 4
help ... 4
HELP! - Formula suggestion 7

Back
Top