Retreive data from different workbooks

[Spreeadsheets]

Dear all,

I'm very new in VBA.I've been running on too little sleep and too much panic which has probably been the cause of my frantic cries for help on here.

I have a question about using 2 workbooks. Worbook1 (activeworkbook)is my BOM. Worbook2 is my database. The logic is:
Book1: If colE is "U",pick ColA then goto BOOK2 search ColA in TableU for matching then retreive ColA,B,C,F to BOOK1 in Col G,H E etc...
IF ColE is "C",pick ColB and ColC then goto BOOK2 search in TableC, ColB,ColC for matching then retreive ColA,B,C,F in BOOK1 of Col G,H E etc...

Something like IF(E2="U",VLOOKUP(A2,'[C:\Test\BOOK2.xls]!Sheet1'TableU,False), IF(E2="C",VLOOKUP(.........

For this code below, I have problems:
1- Error 91
2- can retreive a little with a database have less 300 rows. The fact, my database have each sheets 50,000 rows
3- How the VBA can search like a wildcard ?? If in my BOM have a part 74HC00, I want the VBA can retreive all possile parts in database as
74HC00A, 74HC00AN etc...

Sub BOM()
Dim rng, rng1, rng2, rngTable As Range
Dim wkbk

Set wkbk = Workbooks.Open("C:\Test\testing\Database.xls&quot
nmArray = Array("TableU", "TableQD", "TableC", "TableL&quot

Set OP_Ref = wkbk.Names(nmArray(LBound(nmArray))).RefersToRange
Set QD_Ref = wkbk.Names(nmArray(LBound(nmArray) + 1)).RefersToRange
Set Cap_Ref = wkbk.Names(nmArray(LBound(nmArray) + 2)).RefersToRange
Set L_Ref = wkbk.Names(nmArray(LBound(nmArray) + 3)).RefersToRange

Workbooks("testing.xls&quot.Activate
Set rng = ActiveSheet.Range(Range("E15&quot, Range("E15&quot.End(xlDown))

j = 15 ' Always starting at row 15
For Each cell In rng
Set rng1 = Nothing
Set rng2 = Nothing
'Select Case UCase(Left(cell.Value, 1))
Select Case cell
Case "C"
Set rng1 = Cells(cell.Row, "B&quot
Set rng2 = Cells(cell.Row, "C&quot
Set rngTable = Cap_Ref
Case "D", "Q"
Set rng1 = Cells(cell.Row, "A&quot
Set rngTable = QD_Ref
Case "L"
Set rng1 = Cells(cell.Row, "B&quot
Set rng2 = Cells(cell.Row, "E&quot
Set rngTabe = L_Ref
Case "U"
Set rng1 = Cells(cell.Row, "A&quot
Set rngTable = OP_Ref
End Select

Set rng3 = rngTable.Columns(1).Cells

For Each cell1 In rng3
iloc = InStr(cell1, rng1)
If iloc = 1 Then
For i = 1 To 19
Cells(j, i).Value = cell1.Offset(0, i - 1).Value
j = j + 1
Next
End If
If Not rng2 Is Nothing Then
iloc = InStr(cell1, rng2)
If iloc = 1 Then
For i = 1 To 19
Cells(j, i).Value = cell1.Offset(0, i - 1).Value
j = j + 1
Next
End If
End If
Next
Next
wkbk.Close

End Sub


Thanks in advance,