PDA

View Full Version : Solved: worksheetfunction.vlookup



ValerieT
04-09-2013, 01:19 AM
Hello

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


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


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

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

thanks

mdmackillop
04-09-2013, 05:06 AM
No changes; this works for me, but personally, I would not use Go as a variable name
Can you post a sample workbook?
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

ValerieT
04-09-2013, 05:59 AM
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
04-09-2013, 06:05 AM
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

JKwan
04-09-2013, 06:15 AM
you can add in a check to see if the cell is #N/A

If Not WorksheetFunction.IsNA(Cells(Go, 8)) then
Cells(Go, 20) = WorksheetFunction.VLookup(Cells(Go, 8), Columns("C:F"), 4, 0)

ValerieT
04-09-2013, 06:29 AM
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


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

snb
04-09-2013, 06:39 AM
This probably suffices:


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

ValerieT
04-09-2013, 06:51 AM
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
04-09-2013, 08:05 AM
Sub M_snb()
[U1:U9]=[if(U1:U9="","",If(iserror(U1:U9),"",index(C1:F200,match(H1:H9,C1:C200,0),4)))]
End Sub