Runtime error 1004

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.
[VBA]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
[/VBA]

I then tried to initialise the class using
[VBA]
Application.WorksheetFunction.VLookup.Activate
[/VBA]
It then reports the error "argument non optional"

can anyone suggest a reason for it, or a solution please?

regards

Dave

Where is it erroring?

What do you think this line does?

[vba]
DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 1, False)
[/vba]

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.

it is erroring on that exact line
[VBA]

DEFtask = Application.WorksheetFunction.VLookup(Task, myrange, 1, False)

[/VBA]

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.

That means that the value is not matching. Are you trying to match numeric data by any chance?

Quote:

---

Originally Posted by Aflatoon

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.

If you use:
[vba]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 [/vba]

is the text correct? Also, I assume that the values are in column B on that sheet?

I now have this,
but after the msgbox I still get the 1004 runtime error.

[VBA]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[/VBA]

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.

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

No, VLOOKUP will only look in the first column. If you want to search the entire range, use the Find method:
[vba]Set rFound = myRange.Find(what:=Task, lookin:=xlvalues, lookat:=xlwhole, matchcase:=false)[/vba] for example.

....