vlookup in VBA

[devnext]

i can´t get this to work:
Dim count As Integer
Dim n As Integer
Dim table As String
Dim look As String
Dim column As Integer

count = Workbooks("BDs").Worksheets("bd_lx")._
Range("A2").Value
column = 3
For n = 4 To count
look = Workbooks("BDs").Worksheets("bd_lx")._
Range("B" & n).Value
table = Workbooks("BDs").Worksheets("bd_lx")._
Range("A" & n).Value

Application.WorksheetFunction._
VLookup(look, tabela, column, False).Select
Selection.Copy

can anyine see where is the error?

thanks

 

[J.E. McGimpsey]

Couple of things:

1) In many versions of XL, calling VLookup as a method of the
WorksheetFunctions collection just doesn't work (it's a bug). You
can use Application.VLookup() instead.

2) VLookup expects a range as the second argument. You're passing a
value. If Workbooks("BDs").Worksheets("bd lx").Range("A" & n).Value
contains a string with the table address, pass the argument as

Range(tabela)

instead.

3) VLookup returns either a value or the #N/A error, not a range
that can be selected. I can't tell from you post whether you expect
VLookup to return the address of a cell that can be copied, or if
you just want to copy the result.

 

[Guest]

thanks.
that worked partialliy.

the new error encountered is that vlookup doesn´t accept
a variable range. at least i think that's the problem.

can we do something about it?

 

[J.E. McGimpsey]

Depends. What do you mean by "variable range"?

What are the values of the arguments you're passing to VLookup?