PDA

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
....