VLOOKUP in VBA

[helderw]

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.

...
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"


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 ;)

[patel]

what's nextrowH ?

[helderw]

nextrowH are defined previously in the code.

In this case nextrowH is the total number of filled rows of Sheet2 and is defined as:


nextrowH = Application.WorksheetFunction.CountA(Range("B:B")) + 1

[snb]

Sub tst()
On Error Resume Next
c00 = "searchstring"

c01 = c00 & " has not been found"
c01 = Sheet1.Name & Sheet1.Cells.Find(c00, , xlValues, 1).Address
If Err.Number <> 0 Then c01 = Sheet1.Name & Sheet1.Cells.Find(c00, , xlvalues,1).Address

MsgBox c01
End Sub

[patel]

nextrowH are defined previously in the code.

attach please your file

[Bob Phillips]

Look at Find in VBA help, it shows you how to do what you need. Just test the first sheet, if not okay, test the second.

[helderw]

First of all, thank you to all of you for your support.

Because it's a little bit difficult for me to explain the problem I have, I've attached the file (a simplified version) where you can run and see all the code.

To run the macro you should use the "Imprimir ESM" CommandButton on Sheet1.

The ESM number correspondes to the values on column "B". Sometimes I want values from Sheet1 and other times from Sheet2.

To help you out, the macro should work properly for the values, for exemple, 12118 , 12116 and 12108. At ths moment it only works with the last one.

This is one of the reasons that makes me think that if the value is formated as text or as number it will be recognised or not.

But right now I'm a little bit lost :dunno

Can you help me?

[Bob Phillips]

Private Sub cmdPrintOK_Click()

Dim inputESM As Long
Dim iRow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

inputESM = Val(txbInputESM.Text)

'Determina o numero máximo de linhas da folha "Histórico"
Set ws = Worksheets("Sheet1")
iRow = FindESM(ws.Columns(2), inputESM)
If iRow = 0 Then

Set ws = Worksheets("Por sair...")
iRow = FindESM(ws.Columns(2), inputESM)
If iRow = 0 Then

Set ws = Worksheets("Sheet2")
iRow = FindESM(ws.Columns(2), inputESM)
End If
End If

If iRow > 0 Then

MsgBox inputESM & " found on " & ws.Name & " in row " & iRow, vbInformation + vbOKOnly, "Find ESM"
Else

MsgBox inputESM & " not found", vbCritical + vbOKOnly, "Find ESM"
End If

Unload printESMform

End Sub

Private Function FindESM(LookIn As Range, ESM As Long) As Long
Dim cell As Range

Set cell = LookIn.Find(ESM, LookIn.Cells(1, 1))
If Not cell Is Nothing Then FindESM = cell.Row
End Function

[helderw]

xld, my friend, thank you a lot for your code.

I'm amazed how you did the same thing in a tottaly different way. I have a lot to learn.

However I didn't quite understand how the function "FindESM" works. Could you please explain it to me?

Once again thank's a lot.

[Bob Phillips]

That is how I was suggesting doing it in my previous response.

It just uses the VBA Find function to look in the specified column for the specified value. If found, it returns the row number found in, otherwise it returns 0; the calling procedure determines what to do with that result.

[helderw]

It's much easier this way. Thank you one more time.

Meanwhile I have another problem... Maybe you could help me.

I want to make a print preview of "Capa" woorksheet but for some reason it doesn't work.

I've introduced the following code inside the If cycle you made:


If iRow > 0 Then

MsgBox inputESM & " found on " & ws.Name & " in row " & iRow, vbInformation + vbOKOnly, "Find ESM"

Worksheets("Capa").Range("U1") = ws.Range("B" & [iRow]).Value
'Prints "Capa"
If cbxPrePrint.Value = True Then
Sheets("Capa").PrintOut Preview:=True
'Else
'Sheets("Capa").PrintOut
End If

'Deletes "Capa" info
Worksheets("Capa").Range("U1,U2,U3,N5,N6,T7,P30,P31,P32,M8,N8,O8,P8") = ""

Else

MsgBox inputESM & " not found", vbCritical + vbOKOnly, "Find ESM"
End If

Unload printESMform


When I select in the ESMform the checkbox "Pré-visualizar" (=preview) I should be able to preview the print area.

I think the problem may be because this instruction is inside the If loop.

Do you have any sugestion?

[Bob Phillips]

It is probably because the userform is still loaded. Try moving the unload to before the printpreview If statement.

If that doesn't work, post the workbook and we can take a look.

[helderw]

I had already tried to move the Unload but without sucess.

It seems it is in a "infinite loop".

Please could you take a look? Thanks a lot.

[helderw]

Does anyone have any sugestion regarding the print preview issue?

Thanks!

[mancubus]

as per xld's suggestion:


If cbxPrePrint.Value = True Then
Unload Me
Sheets("Capa").PrintOut Preview:=True
Else
Sheets("Capa").PrintOut
End If

[snb]

sub snb()
Hide
Sheets("Capa").PrintOut cbxPrePrint.Value
end sub

[helderw]

as per xld's suggestion:


If cbxPrePrint.Value = True Then
Unload Me
Sheets("Capa").PrintOut Preview:=True
Else
Sheets("Capa").PrintOut
End If


I have already tried that but it doesn't work. I mean, the "print Preview" menu doesn't show up. You can see this if you run the file I attached before.

Thanks anyway.

[Bob Phillips]

Try this