Solved: worksheetfunction.vlookup

[ValerieT]

Hello

I can't manage to write parameters for worksheetfunction.vlookup (Debug let go but error message when running)

[vba]
While Go < Lignes
Go = Go + 1
Cells(Go, 20) = WorksheetFunction.VLookup(Cells(Go, 8), Columns("C:F"), 4, 0)
wend
[/vba]

(equivalent of: [vba]Cells(Go, 20).FormulaR1C1 = "=VLOOKUP(RC[-12],C[-17]:C[-14],4,0)"[/vba]

I think it's because of my variable Go, representing the line number, how should I write it?

thanks

[mdmackillop]

No changes; this works for me, but personally, I would not use Go as a variable name
Can you post a sample workbook?
[VBA]Sub Test()
Dim Go As Long, Lignes As Long
Lignes = 10
While Go < Lignes
Go = Go + 1
Cells(Go, 20) = WorksheetFunction.VLookup(Cells(Go, 8), Columns("C:F"), 4, 0)
Wend
End Sub[/VBA]

[ValerieT]

Thanks for your answer

I realize my mistake: the code is OK but I have a runtime error when the search is unsuccessful. (equivalent of "#N/A").... Is there a way to keep going anyway?

I apologize in advance, it is probably obvious to you..

[mdmackillop]

[VBA]Sub Test()
Dim Go As Long, Lignes As Long
Lignes = 10
On Error Resume Next
While Go < Lignes
Go = Go + 1
Cells(Go, 20) = WorksheetFunction.VLookup(Cells(Go, 8), Columns("C:F"), 4, 0)
Wend
On Error GoTo 0
End Sub
[/VBA]

[JKwan]

you can add in a check to see if the cell is #N/A
[VBA]
If Not WorksheetFunction.IsNA(Cells(Go, 8)) then
Cells(Go, 20) = WorksheetFunction.VLookup(Cells(Go, 8), Columns("C:F"), 4, 0)

[/VBA]

[ValerieT]

[vba]Sub Test()
Dim Go As Long, Lignes As Long
Lignes = 10
On Error Resume Next
While Go < Lignes
Go = Go + 1
Cells(Go, 20) = WorksheetFunction.VLookup(Cells(Go, 8), Columns("C:F"), 4, 0)
Wend
On Error GoTo 0
End Sub
[/vba]

As I said, so obvious when you know... THANKS!

[snb]

This probably suffices:

[vba]
Sub M_snb()
[U1:U9]=[if(U1:U9="","",index(C1:F200,match(H1:H9,C1:C200,0),4))]
end sub
[/vba]

[ValerieT]

Well no because the #N/A is on the info to be returned, not the one tested. And I gave a simple example, but in fact it's embedded in a huge process with a lot of unknown. (This is why I am using variable position)

But thanks to you, I have 2 new items to discover and study (index and match)

[snb]

[VBA]Sub M_snb()
[U1:U9]=[if(U1:U9="","",If(iserror(U1:U9),"",index(C1:F200,match(H1:H9,C1:C200,0),4) ))]
End Sub [/VBA]