Vlookup entire row with match 2 criteria

[dingdang]

Hi,

Need vlookup or if any other formula/macro to get entire row from 2 input data which match with data in 2nd sheet and copy entire row.

attached file for your ref. data input in A3 and B3 should match with data in "main data" sheet and if match then required entire row copy to D3.

Pls help

https://dl.dropbox.com/u/66400357/test.xlsx

[CatDaddy]

[VBA]Sub alex()
Dim cell As Range
Dim lr As Long, lr2 As Long
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
str1 = Range("A" & lr).Value
str2 = Range("B" & lr).Value
Sheets(2).Activate
lr2 = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("E2:E" & lr2)
If cell.Text = str2 And cell.Offset(0, 1).Text = str1 Then
Range("A" & cell.Row & ":I" & cell.Row).Copy Destination:=Sheets(1).Range("D" & lr)
End If
Next cell
Sheets(1).Activate
End Sub[/VBA]

[dingdang]

YES, Perfect its work ...Thanks a lot

[dingdang]

Sir,

one more help required. not getting data if input is in number format from main data also i have small change in code which i want to copy into new sheet3 instead of same sheet in D column

Copy Destination:=Sheets(3).Range("A" & lr)

[CatDaddy]

[vba]Sub alex()
Dim cell As Range
Dim lr As Long, lr2 As Long
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
str1 = Range("A" & lr).Value
str2 = Range("B" & lr).Value
Sheets(2).Activate
lr2 = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("E2:E" & lr2)
If CStr(cell.Value) = str2 And CStr(cell.Offset(0, 1).Value) = str1 Then
Sheets.add After:=Sheets(Sheets.Count)
lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets(2).Activate
Range("A" & cell.Row & ":I" & cell.Row).Copy Destination:=Sheets(Sheets.Count).Range("A" & lr)
End If
Next cell
Sheets(1).Activate
End Sub
[/vba]

[dingdang]

Sir,

If I input data from A3:B8 and run macro why its getting only data for last row and not for all. given example below for your ref. Pls help

Also can we create a simple user form for ID no and Amt with user can only input the data and will get the entire row from main data in background instead of ruing macro every time.

input Data
id no amt
440 10,000.00
420151 21000.00
3065 2093.00
382707 16622.00
165303 31230.00

AT1 600532002 400111011 06062012 16,622.00 382707 0330167600 11 00000622000000000000000

[CatDaddy]

post a sample workbook for me and i will finish this up for you and make you a userform

[dingdang]

Thank u !!

attached workbook for your ref. Pls do the needful.

https://dl.dropbox.com/u/66400357/test.xlsx

[CatDaddy]

In Standard Module:
[VBA]Public INPUT1 As String
Public INPUT2 As String
Public FLAG As Boolean
Sub alex()
helper1
End Sub
Sub form()
Do While FLAG = False
UserForm1.Show
helper1 INPUT1, INPUT2
Loop

End Sub
Private Sub helper1(Optional S1 As String, Optional S2 As String)
Dim lr, lr3 As Long
Dim i As Integer
Dim str1, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
lr3 = Range("F" & Rows.Count).End(xlUp).Row + 1
If S1 <> "" And S2 <> "" Then
str1 = S1
str2 = S2
helper2 str1, str2, lr3
Else
For i = 3 To lr
Sheets(1).Activate
str1 = Range("A" & i).Value
str2 = Range("B" & i).Value
helper2 str1, str2, lr3
lr3 = lr3 + 1
Next i
End If
Sheets(1).Activate
End Sub
Private Sub helper2(ByVal str1 As String, ByVal str2 As String, lr As Long)
Dim lr2 As Long
Dim cell As Range
ActiveWorkbook.Sheets(2).Activate
lr2 = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("E2:E" & lr2)
If CStr(cell.Value) = str2 And CStr(cell.Offset(0, 1).Value) = str1 Then
Range("A" & cell.Row & ":I" & cell.Row).Copy Destination:=Sheets(1).Range("F" & lr)
Exit For
End If
Next cell
End Sub[/VBA]

In UserForm Module:
[VBA]Private Sub UserForm_Initialize()
Label1.Caption = "INPUT 1"
Label2.Caption = "INPUT 2"
CommandButton1.Caption = "RETRIEVE DATA"
CommandButton2.Caption = "NEXT ENTRY"
TextBox1.Text = ""
TextBox2.Text = ""
FLAG = False
End Sub
Private Sub CommandButton1_Click()
If TextBox1.Text = "" Or TextBox2.Text = "" Then
MsgBox ("Missing Data Fields")
Exit Sub
Else
INPUT1 = TextBox1.Text
INPUT2 = TextBox2.Text
Me.Hide
End If
End Sub
Private Sub CommandButton2_Click()
TextBox1.Text = ""
TextBox2.Text = ""
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
FLAG = True
Unload Me
End Sub[/VBA]

[dingdang]

Sir, Greeee8 Job. u r simply the great..Thanks a lot.

small help required. if userform1 closed and again run macro form its not opening, i need to close and open the file then its opening also is it possible to move cursor after enter on retrivedata button to input 1 , basically i just want to use only numeric keyboard without using mouse which is time consuming.

pls guide.