Working with 2 workbooks (2nd tried)

[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,

 

[melone]

Are you working in Excel? If so, why not use the Vlookup function?

 

[Spreeadsheets]

Hi melone,

The reason I don't want to use VLOOKUP as I already mention
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...

Even VLOOKUP, INDEX and MATCH doesn't doing good job for search. They're good for numbers, matching. The numbers can be between or approxinate but not for STRING

In my code, I have problems dealing with 2 workbooks have multiple worksheets. The loop confused in what workbook is ACTIVE. The debug F8 tell me that.

How do I do it? I'm breaking my head over it !!!!!!!!!!!!!

Thanks,

 

[joerd]

Haven't got much time so I can help you only with the last one: try the LIKE operator (result = string Like pattern, where result=true if string matches pattern).

Error 91 is
"Object variable or With block variable not set"
This may due to one of the variables rng1, rng2, rngTable not being set because none of the Cases was valid. You can use a Case Else statement to avoid this. Also, I spot a typo in Case "L" where you have rngTabe instead of rngTable.

It's difficult to figure out what else goes wrong, but I'll give it a try when I have time.
Regards,

Joerd