Runtime error 1004

[dedmonds]

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

[Bob Phillips]

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.

[dedmonds]

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.

[Aflatoon]

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

[dedmonds]

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]

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?

[dedmonds]

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]

[Aflatoon]

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]

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]

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.

[dedmonds]

....