Vlookup VBA error if value not found

[evaluna]

Hi,
I am new to the forum and also to creating macros in excel with vba.

I have a similar problem to many people around this forum, but the answers i read didn't seem to help my case. The thing is that i have two sheets which have tens of thousand lines each. In one of them I have the id_number of water pipes, and on another I have the measured presure (the thing is that the presure is not measured in all pipes, so not all have a correspondence).

I used this vlookup to match the presure to the pipe. I works perfectly until it encounters a pipe with no measured presure, then there is an error and it crashes. I tried with IsError in my function, but function crashes and exits at the line before it reaches it.

I also tried to use Application.VLookup() instead of Application.WorksheetFunction.VLookup() but it crashed too, give me an Error 13.

Please help, I dont know where more to look for answer. Thanks!


Sub prueba()


Dim result As String
Dim shcalc As Worksheet
Dim shvalores As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

Set shcalc = ActiveWorkbook.Sheets("Calculo")
Set shvalores = ActiveWorkbook.Sheets("Variabilidad")


i = 3

While shcalc.Cells(i, 1).Value <> Empty

result = 0

result = Application.WorksheetFunction.VLookup(shcalc.Cells(i, 1), shvalores.Range("A2:B31"), 2, False)

If IsError(result) Then
result = 0

ElseIf result > 0 Then
shcalc.Cells(i, 37).Value = result

End If

i = i + 1

Wend



Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True



End Sub

 

[ProfessorPorcupine]

I stopped using vlookup in VBA code as it can cause problems and can be slow. Consider using the Find command. Just find the row a certain value is on, and then use that to pull that value. Much faster and more reliable. Just be sure to specify to look for the whole value, or else it might find just part of the value in the wrong cell.

Otherwise, try adding On Error Resume Next before the vLookup and On Error Go To 0 after the vLookup, which should solve your problem.

 

[ProfessorPorcupine]

I meant On Error GoTo Next.

See sample below:
While shcalc.Cells(i, 1).Value <> Empty
result = 0
On Error Resume Next
result = Application.WorksheetFunction.VLookup(shcalc.Cells(i, 1), shvalores.Range("A10:B31"), 2, False)
On Error GoTo 0
shcalc.Cells(i, 37).Value = result

i = i + 1
Wend

 

[AlphaFrog]

Your original code works if you...

1. declare Result as type Variant to accept the error or lookup value
2. use Application.Vlookup and not Application.Worksheetfunction.Vlookup

[COLOR=darkblue]Dim[/COLOR] Result [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]


Result = Application.VLookup(shcalc.Cells(i, 1), shvalores.Range("A2:B31"), 2, [COLOR=darkblue]False[/COLOR])
[COLOR=darkblue]If[/COLOR] IsError(Result) [COLOR=darkblue]Then[/COLOR]
    [COLOR=green]'No match found[/COLOR]
    Result = 0
[COLOR=darkblue]Else[/COLOR]
    [COLOR=green]'Match found[/COLOR]
    shcalc.Cells(i, 37).Value = Result
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

]

 

[evaluna]

Thank you so much. It works perfectly now!
Still, I will try to see if I can figure out how the Find Command works and use it instead.

 

[evaluna]

Why doesn't this work?
I see the silly mistake I made, the importante part is missing..... Can't delete this post!
The use of find is not clear to me when i want to use it in this kind of loop thing.

Sub prueba2()


Dim result As Variant
Dim shcalc As Worksheet
Dim shvalores As Worksheet


Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

Set shcalc = ActiveWorkbook.Sheets("Calculo")
Set shvalores = ActiveWorkbook.Sheets("Variabilidad")


i = 3

While shcalc.Cells(i, 1).Value <> Empty
result = 0
result = Application.WorksheetFunction.if(IsError(Application.WorksheetFunction.Find(shcalc.Cells(i, 1), shvalores.Range("A10:B31"))) = True, 0, shcalc.Cells(i, 37))

i = i + 1
Wend

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True



End Sub

 

[evaluna]

Okie, so I guess I am not aloud to delete or edit my previous post.

I was really not getting the workings of the find function when i posted it earlier. I finally have it, so I will leave the working code here to make up for the mistaken one up there.


Sub prueba2()


Dim result As Variant
Dim shcalc As Worksheet
Dim shvalores As Worksheet
Dim rowx As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

Set shcalc = ActiveWorkbook.Sheets("Calculo")
Set shvalores = ActiveWorkbook.Sheets("Variabilidad")


i = 3

While shcalc.Cells(i, 1).Value <> Empty
result = 0
rowx = 0
On Error Resume Next


rowx = shvalores.Range("A1:B31").Find(What:=shcalc.Cells(i, 1), lookat:=xlPart).Row

result = shvalores.Cells(rowx, 2)


On Error GoTo 0
shcalc.Cells(i, 37).Value = result


i = i + 1
Wend


Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True



End Sub

 

[AlphaFrog]

Here's another for what it's worth.

[COLOR=darkblue]Sub[/COLOR] prueba3()
    
    [COLOR=darkblue]Dim[/COLOR] result    [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] shcalc    [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] shvalores [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] cell      [COLOR=darkblue]As[/COLOR] Range
    
    Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] shcalc = ActiveWorkbook.Sheets("Calculo")
    [COLOR=darkblue]Set[/COLOR] shvalores = ActiveWorkbook.Sheets("Variabilidad")
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] shcalc.Range("A3", shcalc.Range("A" & Rows.Count).End(xlUp))
    
        [COLOR=darkblue]Set[/COLOR] result = shvalores.Range("A1:A31").Find(What:=cell.Value, _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlPart, _
                                                    SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, _
                                                    MatchCase:=False)
    
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] result [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] cell.Offset(, 36).Value = result.Offset(, 1).Value
    
    [COLOR=darkblue]Next[/COLOR] cell
    
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]