VBA Code Help

[ashraf999]

I need to use the following excel code in a userform:

"=IFERROR(INDEX('C:\[Query 4.xlsx]Complex Query 4'!$AB$2:$AB$1008,MATCH($B8&TEXT1,'C:\[Query 4.xlsx]Query 4'!$E$2:$E$1008&'C:\[Query 4.xlsx]Query 4'!$A$2:$A$1008,0)),"")"

Where TEXT1:

"=LEFT('Project Allocation Sheet'!J$6,2)&TEXT(LEFT(SUBSTITUTE(SUBSTITUTE(LEFT('Project Allocation Sheet'!J$6,FIND(" ",'Project Allocation Sheet'!J$6&" ")-1),LEFT('Project Allocation Sheet'!J$6,2),""),"-",REPT(" ",10)),10),"000")&"-"&TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT('Project Allocation Sheet'!J$6,FIND(" ",'Project Allocation Sheet'!J$6&" ")-1),LEFT('Project Allocation Sheet'!J$6,2),""),"-",REPT(" ",10)),10),"000")"

What is the VBA code so I could use it in a userform?

Thank you

[Bob Phillips]

Try this

[vba]

Const ws As String = "'C:\[Query 4.xlsx]Complex Query 4'!"
Const wsE As String = ws & "$E$2:$E$1008"
Const wsAB As String = ws & "$AB$2:$AB$1008"
Dim tmp As Variant
Dim res As Variant

With Worksheets("Project Allocation Sheet")

tmp = Replace(Replace( _
Left$(.Range("J6").Value, InStr(.Range("J6").Value & " ", " ") - 1), _
Left$(.Range("J6").Value, 2), ""), _
"-", " ")
tmp = Left$(.Range("J6").Value, 2) & _
Format(Left$(tmp, 10), "000") & "-" & _
Format(Right$(tmp, 10), "000")
End With

res = Application.Evaluate("IFERROR(INDEX(" & wsAB & "," & _
"MATCH($B8&" & tmp & "," & wsE & "&" & wsAB & ",0)),"""")")
[/vba]

[ashraf999]

Try this

[vba]

Const ws As String = "'C:\[Query 4.xlsx]Complex Query 4'!"
Const wsE As String = ws & "$E$2:$E$1008"
Const wsAB As String = ws & "$AB$2:$AB$1008"
Dim tmp As Variant
Dim res As Variant

With Worksheets("Project Allocation Sheet")

tmp = Replace(Replace( _
Left$(.Range("J6").Value, InStr(.Range("J6").Value & " ", " ") - 1), _
Left$(.Range("J6").Value, 2), ""), _
"-", " ")
tmp = Left$(.Range("J6").Value, 2) & _
Format(Left$(tmp, 10), "000") & "-" & _
Format(Right$(tmp, 10), "000")
End With

res = Application.Evaluate("IFERROR(INDEX(" & wsAB & "," & _
"MATCH($B8&" & tmp & "," & wsE & "&" & wsAB & ",0)),"""")")
[/vba]

Thanks. For some reason, the userform is returning nothing although the formula works fine. When I drag the formula across the remaining cells, all the results are returned accurately. I don't know what I am missing

[ashraf999]

Try this

[vba]

Const ws As String = "'C:\[Query 4.xlsx]Complex Query 4'!"
Const wsE As String = ws & "$E$2:$E$1008"
Const wsAB As String = ws & "$AB$2:$AB$1008"
Dim tmp As Variant
Dim res As Variant

With Worksheets("Project Allocation Sheet")

tmp = Replace(Replace( _
Left$(.Range("J6").Value, InStr(.Range("J6").Value & " ", " ") - 1), _
Left$(.Range("J6").Value, 2), ""), _
"-", " ")
tmp = Left$(.Range("J6").Value, 2) & _
Format(Left$(tmp, 10), "000") & "-" & _
Format(Right$(tmp, 10), "000")
End With

res = Application.Evaluate("IFERROR(INDEX(" & wsAB & "," & _
"MATCH($B8&" & tmp & "," & wsE & "&" & wsAB & ",0)),"""")")
[/vba]

When I use:

MsgBox res, I get a "Type Mismatch" error

[Bob Phillips]

Post the workbook.

[ashraf999]

Post the workbook.

Find attached a sample

[ashraf999]

Post the workbook.

and the data worksheet