View Full Version : Runtime error 1004
dedmonds
09-07-2011, 03:10 AM
Hi I am trying to target the current selection in a list box in order to run a VLOOKUP to return offset colum results to text boxes on the same user form.
Using the following code I get runtime error 1004.
Private Sub Category_click()
Dim Task As String
If pref_addr_1.Value <> 0 Then
Task = Category.Value
Call tasksearch(Task)
End If
End Sub
Sub tasksearch(Task)
Dim DEFtask
Dim DEF
Dim myrange
Set myrange = Worksheets("full_list").Range("B2:N145")
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 1, False)
DEF = DEF + DEFtask
Worksheets("Full_list").Range("B3") = DEF
I then tried to initialise the class using
Application.WorksheetFunction.VLookup.Activate
It then reports the error "argument non optional"
can anyone suggest a reason for it, or a solution please?
regards
Dave
Bob Phillips
09-07-2011, 03:14 AM
Where is it erroring?
What do you think this line does?
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 1, False)
By looking up column 1, if it matches, all you will get is the same value you looked up.
The last bit is just bad syntax, will not, cannot work.
dedmonds
09-07-2011, 03:20 AM
it is erroring on that exact line
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 1, False)
I thought the line was actually running the VLOOKUP - it doesnt currently matter what column is returned I just want it to return something, then I can alter what it return later. If you get me.
Aflatoon
09-07-2011, 03:33 AM
That means that the value is not matching. Are you trying to match numeric data by any chance?
dedmonds
09-07-2011, 03:42 AM
That means that the value is not matching. Are you trying to match numeric data by any chance?
No I have a list of categories as a dynamic range. That range then appears in the listbox
The Categories are names of departments no numerics in there.
Aflatoon
09-07-2011, 03:47 AM
If you use:
Private Sub Category_click()
Dim Task As String
If pref_addr_1.Value <> 0 Then
Task = Category.Value
msgbox "Task is " & Task
Call tasksearch(Task)
End If
End Sub
is the text correct? Also, I assume that the values are in column B on that sheet?
dedmonds
09-07-2011, 04:12 AM
I now have this,
but after the msgbox I still get the 1004 runtime error.
Private Sub Category_click()
Dim Task As String
If pref_addr_1.Value <> 0 Then
Task = Category.Value
MsgBox "Task is " & Task
Call tasksearch(Task)
End If
End Sub
Sub tasksearch(Task)
Dim DEFtask
Dim DEF
Dim myrange
Set myrange = Worksheets("full_list").Range("B2:N145")
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 2, False)
DEF = DEF + DEFtask
Category.Value = DEF
End Sub
Aflatoon
09-07-2011, 04:14 AM
I think we would need to see the workbook. You will only get that error if the text does not match an entry in column B of the range you specified.
dedmonds
09-07-2011, 04:26 AM
I thought it would search the entire Range("B2:N145").
If that is the case, I have some major restructuring of the USQL queries in that worksheet before I can proceed with the user form.
I will come back to you if it doesn't work.
thanks for your help.
Dave
Aflatoon
09-07-2011, 04:41 AM
No, VLOOKUP will only look in the first column. If you want to search the entire range, use the Find method:
Set rFound = myRange.Find(what:=Task, lookin:=xlvalues, lookat:=xlwhole, matchcase:=false) for example.
dedmonds
09-07-2011, 08:23 AM
....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.