I put in

Dim xxx as variant

and it solved the problem

Thanks for all the help

"Luke M" wrote:

No, its simply a variant to hold whatever the result of the vlookup is
(whether its an error, a number, a string, etc)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

Do I need to declare xxx as something?

"p45cal" wrote:


Very likely because what it's looking for isn't to be found.
I tried this:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row,
"A"), srccom, 5, 0)
and it worked, but then gave the error you describe if what was sought
didn't exist.
Try the likes of this:Dim srccom As Range
Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100")
xxx = Empty
On Error Resume Next
xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5,
0)
On Error GoTo 0 'cancels the On Error Resume Next
If Not IsEmpty(xxx) Then ActiveCell.Value = xxx


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806