Hello my friends.
Everyone knows the "VLOOKUP" function in excel right? So what I want to do is the same function but in VBA and with two ranges insted of only one.
Goal: from an input value from a TextBox, I want to search it in two different sheets knowing that the value will be only in one of them.
To do that I made the following code that should do something like this: First it looks in Sheet1 that has the maximum of 25 rows. However, if it founds an empty cell then it should interrupt the cycle and go search the value in Sheet2. The same way like in Sheet1, if founds the value than interrupts the cycle but if founds a empty cell then it gives a message that the value wasn't found.
[VBA] ...
Worksheets("Sheet1").Activate
For irowP = 4 To 25
If Range("B" & [irowP]) = inputESM Then
GoTo FimSEproj
End If
If Range("B" & [irowP]) = Empty Then
Worksheets("Sheet2").Activate
For irowH = 4 To nextrowH + 1
If Range("B" & [irowH]) = inputESM Then
GoTo FimSEhist
End If
If Range("B" & [irowH]) = Empty Then
MsgBox "ESM " & inputESM & " not found"
GoTo FimSUB
End If
Next irowH
End If
Next irowP
FimSEproj:
irow = irowP
ESMSheet = ActiveSheet.Name
GoTo NewJob
FimSEhist:
irow = irowH
ESMSheet = ActiveSheet.Name
GoTo NewJob
NewJob:
MsgBox "irowP= " & irowP & "irowH= " & irowH & "irow= " & irow & "FolhaESM= " & FolhaESM
FimSUB:
MsgBox "END"
[/VBA]
Result: Everything works great except when the value is on Sheet1. In this case I get the message of "ESM not found". I don't get it!
Please help me