dedmonds
09-09-2011, 02:40 AM
Hello,
I am trying to perform 2 different VLOOKUPs on 2 different user forms the first
Private Sub Category_click()
Dim Task As String
Task = Category.Value
Call tasksearch(Task)
End Sub
Sub tasksearch(Task)
Dim DEFtask
Dim DEFtask1
Dim DEFtask2
Dim DEFtask3
Dim DEFtask4
Dim DEFtask5
Dim DEFtask6
Dim DEFtask7
Dim DEFtask8
Dim DEFtask9
Dim myrange
Application.Workbooks.Open ("G:\EPFinanceLeigh\Suppliers\supplier selection tool data file.xls")
Set myrange = Worksheets("Full_list").Range("A2:M145")
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 2, False)
DEFtask1 = Application.WorksheetFunction.VLookup(Task, myrange, 3, False)
DEFtask2 = Application.WorksheetFunction.VLookup(Task, myrange, 4, False)
DEFtask3 = Application.WorksheetFunction.VLookup(Task, myrange, 5, False)
DEFtask4 = Application.WorksheetFunction.VLookup(Task, myrange, 6, False)
DEFtask5 = Application.WorksheetFunction.VLookup(Task, myrange, 7, False)
DEFtask6 = Application.WorksheetFunction.VLookup(Task, myrange, 8, False)
DEFtask7 = Application.WorksheetFunction.VLookup(Task, myrange, 9, False)
DEFtask8 = Application.WorksheetFunction.VLookup(Task, myrange, 10, False)
pref_supl_name.Value = DEFtask
pref_addr_1.Value = DEFtask1
pref_addr_2.Value = DEFtask2
pref_addr_3.Value = DEFtask3
pref_addr_4.Value = DEFtask4
pref_post.Value = DEFtask5
pref_name.Value = DEFtask6
pref_tel.Value = DEFtask7
pref_fax.Value = DEFtask8
End Sub
Works fine however when I try to run a similar one on another form
Private Sub Secondary_Click()
Dim Task As String
Task = transferred_category.Value
Call tasksearch1(Task)
End Sub
Sub tasksearch1(Task)
Dim SECtask
Dim SECtask1
Dim SECtask2
Dim SECtask3
Dim SECtask4
Dim SECtask5
Dim SECtask6
Dim SECtask7
Dim SECtask8
Dim SECtask9
Dim myrange1
Application.Workbooks.Open ("G:\EPFinanceLeigh\Suppliers\supplier selection tool data file.xls")
Set myrange1 = Worksheets("Sec_list").Range("A2:M145")
SECtask = Application.WorksheetFunction.VLookup(Task, myrange1, 2, False)
SECtask1 = Application.WorksheetFunction.VLookup(Task, myrange1, 3, False)
SECtask2 = Application.WorksheetFunction.VLookup(Task, myrange1, 4, False)
SECtask3 = Application.WorksheetFunction.VLookup(Task, myrange1, 5, False)
SECtask4 = Application.WorksheetFunction.VLookup(Task, myrange1, 6, False)
SECtask5 = Application.WorksheetFunction.VLookup(Task, myrange1, 7, False)
SECtask6 = Application.WorksheetFunction.VLookup(Task, myrange1, 8, False)
SECtask7 = Application.WorksheetFunction.VLookup(Task, myrange1, 9, False)
SECtask8 = Application.WorksheetFunction.VLookup(Task, myrange1, 10, False)
sec_supl_name.Value = SECtask
sec_addr_1.Value = SECtask1
sec_addr_2.Value = SECtask2
sec_addr_3.Value = SECtask3
sec_addr_4.Value = SECtask4
sec_post.Value = SECtask5
sec_name.Value = SECtask6
sec_tel.Value = SECtask7
sec_fax.Value = SECtask8
End Sub
I get blank boxes, can anyone help me as to why?
Dave
I am trying to perform 2 different VLOOKUPs on 2 different user forms the first
Private Sub Category_click()
Dim Task As String
Task = Category.Value
Call tasksearch(Task)
End Sub
Sub tasksearch(Task)
Dim DEFtask
Dim DEFtask1
Dim DEFtask2
Dim DEFtask3
Dim DEFtask4
Dim DEFtask5
Dim DEFtask6
Dim DEFtask7
Dim DEFtask8
Dim DEFtask9
Dim myrange
Application.Workbooks.Open ("G:\EPFinanceLeigh\Suppliers\supplier selection tool data file.xls")
Set myrange = Worksheets("Full_list").Range("A2:M145")
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 2, False)
DEFtask1 = Application.WorksheetFunction.VLookup(Task, myrange, 3, False)
DEFtask2 = Application.WorksheetFunction.VLookup(Task, myrange, 4, False)
DEFtask3 = Application.WorksheetFunction.VLookup(Task, myrange, 5, False)
DEFtask4 = Application.WorksheetFunction.VLookup(Task, myrange, 6, False)
DEFtask5 = Application.WorksheetFunction.VLookup(Task, myrange, 7, False)
DEFtask6 = Application.WorksheetFunction.VLookup(Task, myrange, 8, False)
DEFtask7 = Application.WorksheetFunction.VLookup(Task, myrange, 9, False)
DEFtask8 = Application.WorksheetFunction.VLookup(Task, myrange, 10, False)
pref_supl_name.Value = DEFtask
pref_addr_1.Value = DEFtask1
pref_addr_2.Value = DEFtask2
pref_addr_3.Value = DEFtask3
pref_addr_4.Value = DEFtask4
pref_post.Value = DEFtask5
pref_name.Value = DEFtask6
pref_tel.Value = DEFtask7
pref_fax.Value = DEFtask8
End Sub
Works fine however when I try to run a similar one on another form
Private Sub Secondary_Click()
Dim Task As String
Task = transferred_category.Value
Call tasksearch1(Task)
End Sub
Sub tasksearch1(Task)
Dim SECtask
Dim SECtask1
Dim SECtask2
Dim SECtask3
Dim SECtask4
Dim SECtask5
Dim SECtask6
Dim SECtask7
Dim SECtask8
Dim SECtask9
Dim myrange1
Application.Workbooks.Open ("G:\EPFinanceLeigh\Suppliers\supplier selection tool data file.xls")
Set myrange1 = Worksheets("Sec_list").Range("A2:M145")
SECtask = Application.WorksheetFunction.VLookup(Task, myrange1, 2, False)
SECtask1 = Application.WorksheetFunction.VLookup(Task, myrange1, 3, False)
SECtask2 = Application.WorksheetFunction.VLookup(Task, myrange1, 4, False)
SECtask3 = Application.WorksheetFunction.VLookup(Task, myrange1, 5, False)
SECtask4 = Application.WorksheetFunction.VLookup(Task, myrange1, 6, False)
SECtask5 = Application.WorksheetFunction.VLookup(Task, myrange1, 7, False)
SECtask6 = Application.WorksheetFunction.VLookup(Task, myrange1, 8, False)
SECtask7 = Application.WorksheetFunction.VLookup(Task, myrange1, 9, False)
SECtask8 = Application.WorksheetFunction.VLookup(Task, myrange1, 10, False)
sec_supl_name.Value = SECtask
sec_addr_1.Value = SECtask1
sec_addr_2.Value = SECtask2
sec_addr_3.Value = SECtask3
sec_addr_4.Value = SECtask4
sec_post.Value = SECtask5
sec_name.Value = SECtask6
sec_tel.Value = SECtask7
sec_fax.Value = SECtask8
End Sub
I get blank boxes, can anyone help me as to why?
Dave