View Full Version : VBA Code Help
ashraf999
02-18-2012, 06:58 AM
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
02-18-2012, 08:35 AM
Try this
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)),"""")")
ashraf999
02-19-2012, 12:35 AM
Try this
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)),"""")")
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
02-19-2012, 03:33 AM
Try this
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)),"""")")
When I use:
MsgBox res, I get a "Type Mismatch" error
Bob Phillips
02-19-2012, 04:53 AM
Post the workbook.
ashraf999
02-19-2012, 05:50 AM
Post the workbook.
Find attached a sample
ashraf999
02-19-2012, 05:52 AM
Post the workbook.
and the data worksheet
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.