PDA

View Full Version : VLOOKUP - works on 1 form but not on another


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
Bob Phillips
09-09-2011, 02:42 AM
I don't see how it could work at all. You define the Task variable in one procedure, and use it in another, they are effectively different variables.

Post the workbook.
dedmonds
09-09-2011, 05:32 AM
hello I have solved it by combining the 4 subroutines into 2 as follows -

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 SECtask
Dim SECtask1
Dim SECtask2
Dim SECtask3
Dim SECtask4
Dim SECtask5
Dim SECtask6
Dim SECtask7
Dim SECtask8
Dim SECtask9





Dim myrange
Dim myrange1
Application.Workbooks.Open ("G:\EPFinanceLeigh\Suppliers\supplier selection tool data file.xls")



Set myrange = Worksheets("Full_list").Range("A2:M145")
Set myrange1 = Worksheets("Sec_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)

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)


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

Secondary.sec_supl_name.Value = SECtask
Secondary.sec_addr_1.Value = SECtask1
Secondary.sec_addr_2.Value = SECtask2
Secondary.sec_addr_3.Value = SECtask3
Secondary.sec_addr_4.Value = SECtask4
Secondary.sec_post.Value = SECtask5
Secondary.sec_name.Value = SECtask6
Secondary.sec_tel.Value = SECtask7
Secondary.sec_fax.Value = SECtask8

End Sub

Cheers

Dave