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!
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)
Sub M_snb()
[U1:U9]=[if(U1:U9="","",If(iserror(U1:U9),"",index(C1:F200,match(H1:H9,C1:C200,0),4)))]
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.