Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup erro
Hi
I use VLookp in lines of the programmig however, the code is get same error Option Explici 4 5 8 d0 n1 d0 n d1 n2 d1 n d3 n3 d3 n d4 n4 d4 n d5 n5 d20 erro d6 n6 d7 n7 Sub Macro1( Dim i As Intege Dim a, b As Strin Dim rangea, rangeb As rang Set rangea = range(Cells(2, 4), Cells(20, 5) Set rangeb = range(Cells(2, 8), Cells(20, 8) For i = 0 To 3 a = Cells(2 + i, 8 Cells(2 + i, 9) = iif(IsError(Application.WorksheetFunction.VLookup( a, rangea, 2, False)), 1,_ Application.WorksheetFunction.VLookup(a, rangea, 2, False) Next End Su Run-time error '1004' VLookup method of Range class faile Can someone help me with this |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup erro
Without looking any further maybe too many iiiiii's in IF
-- Don Guillett SalesAid Software "Miguel" wrote in message ... Hi, I use VLookp in lines of the programmig however, the code is get same error. Option Explicit 4 5 8 9 d0 n1 d0 n1 d1 n2 d1 n2 d3 n3 d3 n3 d4 n4 d4 n4 d5 n5 d20 erro d6 n6 d7 n7 Sub Macro1() Dim i As Integer Dim a, b As String Dim rangea, rangeb As range Set rangea = range(Cells(2, 4), Cells(20, 5)) Set rangeb = range(Cells(2, 8), Cells(20, 8)) For i = 0 To 30 a = Cells(2 + i, 8) Cells(2 + i, 9) = iif(IsError(Application.WorksheetFunction.VLookup( a, rangea, 2, False)), 1,_ Application.WorksheetFunction.VLookup(a, rangea, 2, False)) Next i End Sub Run-time error '1004': VLookup method of Range class failed Can someone help me with this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup erro
Try getting rid of .worksheetfunction
Used like application.worksheetfunction.vlookup(), if no match is found, then an error is raised: dim Res as variant on error resume next res = application.worksheetfunction.vlookup(...) if err.num < 0 then 'no match found err.clear end if on error goto 0 But if you use it like: application.vlookup(), an error can be returned: dim res as variant res = application.vlookup(...) if iserror(res) then 'no match found end if I find the second version easier. In fact, instead of having to do the vlookup twice, you may want to rewrite your routine slightly. Option Explicit Sub Macro1() Dim i As Integer Dim a, b As String Dim res As Variant Dim rangea As Range Dim rangeb As Range Set rangea = Range(Cells(2, 4), Cells(20, 5)) Set rangeb = Range(Cells(2, 8), Cells(20, 8)) For i = 0 To 30 a = Cells(2 + i, 8) res = Application.VLookup(a, rangea, 2, False) If IsError(res) Then Cells(2 + i, 9).Value = 1 Else Cells(2 + i, 9).Value = res End If Next i End Sub or: Option Explicit Sub Macro1() Dim i As Integer Dim a, b As String Dim res As Variant Dim rangea As Range Dim rangeb As Range Set rangea = Range(Cells(2, 4), Cells(20, 5)) Set rangeb = Range(Cells(2, 8), Cells(20, 8)) For i = 0 To 30 a = Cells(2 + i, 8) On Error Resume Next res = Application.WorksheetFunction.VLookup(a, rangea, 2, False) If Err.Number < 0 Then Cells(2 + i, 9).Value = 1 Err.Clear Else Cells(2 + i, 9).Value = res End If On Error GoTo 0 Next i End Sub And one more thing: Watchout for this: Dim rangea, rangeb As range This actually declares rangeb as a range, but rangea is declared as a Variant. dim rangea as range, rangeb as range or Dim rangea As Range Dim rangeb As Range would be better Miguel wrote: Hi, I use VLookp in lines of the programmig however, the code is get same error. Option Explicit 4 5 8 9 d0 n1 d0 n1 d1 n2 d1 n2 d3 n3 d3 n3 d4 n4 d4 n4 d5 n5 d20 erro d6 n6 d7 n7 Sub Macro1() Dim i As Integer Dim a, b As String Dim rangea, rangeb As range Set rangea = range(Cells(2, 4), Cells(20, 5)) Set rangeb = range(Cells(2, 8), Cells(20, 8)) For i = 0 To 30 a = Cells(2 + i, 8) Cells(2 + i, 9) = iif(IsError(Application.WorksheetFunction.VLookup( a, rangea, 2, False)), 1,_ Application.WorksheetFunction.VLookup(a, rangea, 2, False)) Next i End Sub Run-time error '1004': VLookup method of Range class failed Can someone help me with this? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup erro
Need to look a little farther, Don - take a look at the Iif function
in VBA Help. I suspect the problem is using the WorksheetFunction object in Application.WorksheetFunction.VLookup() In some versions of XL, the WorksheetFunction object does not have VLOOKUP as a valid property. Using Application.VLookup(...) instead works in all versions. In article , "Don Guillett" wrote: Without looking any further maybe too many iiiiii's in IF |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup erro
Dave, I had forgotten about that.
-- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... There's an immediate if (iif) that works a lot like an If statement inside a cell on a worksheet. Don Guillett wrote: Without looking any further maybe too many iiiiii's in IF -- Don Guillett SalesAid Software "Miguel" wrote in message ... Hi, I use VLookp in lines of the programmig however, the code is get same error. Option Explicit 4 5 8 9 d0 n1 d0 n1 d1 n2 d1 n2 d3 n3 d3 n3 d4 n4 d4 n4 d5 n5 d20 erro d6 n6 d7 n7 Sub Macro1() Dim i As Integer Dim a, b As String Dim rangea, rangeb As range Set rangea = range(Cells(2, 4), Cells(20, 5)) Set rangeb = range(Cells(2, 8), Cells(20, 8)) For i = 0 To 30 a = Cells(2 + i, 8) Cells(2 + i, 9) = iif(IsError(Application.WorksheetFunction.VLookup( a, rangea, 2, False)), 1,_ Application.WorksheetFunction.VLookup(a, rangea, 2, False)) Next i End Sub Run-time error '1004': VLookup method of Range class failed Can someone help me with this? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup erro
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Erro | Excel Discussion (Misc queries) | |||
Erro Msg: Cannot Empty The Clipboard | Excel Discussion (Misc queries) | |||
Macro erro | Excel Discussion (Misc queries) | |||
Excel Erro | Setting up and Configuration of Excel | |||
Erro in Formula, Pleas Help? | Excel Worksheet Functions |