| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2nd Posting - 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
You'll get a quicker response if you post this to the programming group.
microsoft.public.excel.programming "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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
Hi Bob,
I have the following msg for mtchValue = Error 2015. -- Regards, Jeff "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
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" wrote in message ... Hi Bob, I have the following msg for mtchValue = Error 2015. -- Regards, Jeff "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
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" wrote: 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" wrote in message ... Hi Bob, I have the following msg for mtchValue = Error 2015. -- Regards, Jeff "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
Did you correct the wrap-around?
-- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Jeff" wrote in message ... 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" wrote: 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" wrote in message ... Hi Bob, I have the following msg for mtchValue = Error 2015. -- Regards, Jeff "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
I'm not sure understand the "wrap-around"
-- Regards, Jeff "Bob Phillips" wrote: Did you correct the wrap-around? -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Jeff" wrote in message ... 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" wrote: 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" wrote in message ... Hi Bob, I have the following msg for mtchValue = Error 2015. -- Regards, Jeff "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd Posting - Need Help on VBA Procedure
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" wrote in message ... I'm not sure understand the "wrap-around" -- Regards, Jeff "Bob Phillips" wrote: Did you correct the wrap-around? -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Jeff" wrote in message ... 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" wrote: 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" wrote in message ... Hi Bob, I have the following msg for mtchValue = Error 2015. -- Regards, Jeff "Bob Phillips" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtotals by count | Excel Discussion (Misc queries) | |||
| Extract data from Stored procedure | Excel Discussion (Misc queries) | |||
| Posting date | Excel Worksheet Functions | |||
| the owner of posting should be able to delete the posting | Excel Discussion (Misc queries) | |||
| execute stored procedure from excel | Excel Worksheet Functions | |||
Linear Mode
