vlookup in vb.net in retriving data

[GajendraSantosh]

Hello!.
i am trying to retrieve information from .xlsx workbook to vb.net userform using vlookup function . iam not getting the output . where i am wrong would you please guide .
as i am new to .net & vba .

Dim r1, r2, r3, r4, r5, r6 As Long
Dim rng As Range
rng = xlWorkBook.Worksheets(2).Range("A:H")
P1.Text = xlWorkBook.Worksheets(2).Application.VLookup("P1_" & txtRefNo.Text, rng, 1, False)
P2.Text = xlWorkBook.Worksheets(2).Application.VLookup("P2_" & txtRefNo.Text, rng, 1, False)
P3.Text = xlWorkBook.Worksheets(2).Application.VLookup("P3_" & txtRefNo.Text, rng, 1, False)
P4.Text = xlWorkBook.Worksheets(2).Application.VLookup("P4_" & txtRefNo.Text, rng, 1, False)
P5.Text = xlWorkBook.Worksheets(2).Application.VLookup("P5_" & txtRefNo.Text, rng, 1, False)
P6.Text = xlWorkBook.Worksheets(2).Application.VLookup("P6_" & txtRefNo.Text, rng, 1, False)


'On Error Resume Next
If P1.Text = "P1_" & txtRefNo.Text Then
r1 = xlWorkBook.Application.Match(Val(P1.Text), xlWorkBook.Worksheets(2).Columns(1), 0)
txtPDate1.Text = xlWorkBook.Worksheets(2).Range("B" & r1).Value
txtPCash1.Text = xlWorkBook.Worksheets(2).Range("C" & r1).Value
txtPBank1.Text = xlWorkBook.Worksheets(2).Range("D" & r1).Value
txtPChqNo1.Text = xlWorkBook.Worksheets(2).Range("E" & r1).Value
txtPTotal1.Text = xlWorkBook.Worksheets(2).Range("F" & r1).Value
cmbPStatus1.Text = xlWorkBook.Worksheets(2).Range("G" & r1).Value
txtPRemarks1.Text = xlWorkBook.Worksheets(2).Range("H" & r1).Value
End If


'On Error Resume Next
If P2.Text = "P2_" & txtRefNo.Text Then
r2 = xlWorkBook.Application.Match(P2, xlWorkBook.Worksheets(2).Columns(1), 0)
txtPDate2.Text = xlWorkBook.Worksheets(2).cells(r2, "B").Value
txtPCash2.Text = xlWorkBook.Worksheets(2).cells(r2, "C").Value
txtPBank2.Text = xlWorkBook.Worksheets(2).cells(r2, "D").Value
txtPChqNo2.Text = xlWorkBook.Worksheets(2).cells(r2, "E").Value
txtPTotal2.Text = xlWorkBook.Worksheets(2).cells(r2, "F").Value
cmbPStatus2.Text = xlWorkBook.Worksheets(2).cells(r2, "G").Value
txtPRemarks2.Text = xlWorkBook.Worksheets(2).cells(r2, "H").Value
End If


'On Error Resume Next
If P3.Text = "P3_" & txtRefNo.Text Then
r2 = xlWorkBook.Worksheets(2).Application.WorksheetFunction.Match(P3, xlWorkBook.Worksheets(2).Columns(1), 0)
txtPDate3.Text = xlWorkBook.Worksheets(2).cells(r3, "B").Value
txtPCash3.Text = xlWorkBook.Worksheets(2).cells(r3, "C").Value
txtPBank3.Text = xlWorkBook.Worksheets(2).cells(r3, "D").Value
txtPChqNo3.Text = xlWorkBook.Worksheets(2).cells(r3, "E").Value
txtPTotal3.Text = xlWorkBook.Worksheets(2).cells(r3, "F").Value
cmbPStatus3.Text = xlWorkBook.Worksheets(2).cells(r3, "G").Value
txtPRemarks3.Text = xlWorkBook.Worksheets(2).cells(r3, "H").Value
End If