×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Working with 2 workbooks (2nd tried)

Working with 2 workbooks (2nd tried)

Working with 2 workbooks (2nd tried)

(OP)
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")
nmArray = Array("TableU", "TableQD", "TableC", "TableL")

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").Activate
Set rng = ActiveSheet.Range(Range("E15"), Range("E15").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")
            Set rng2 = Cells(cell.Row, "C")
            Set rngTable = Cap_Ref
        Case "D", "Q"
            Set rng1 = Cells(cell.Row, "A")
            Set rngTable = QD_Ref
        Case "L"
            Set rng1 = Cells(cell.Row, "B")
            Set rng2 = Cells(cell.Row, "E")
            Set rngTabe = L_Ref
        Case "U"
            Set rng1 = Cells(cell.Row, "A")
            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,

RE: Working with 2 workbooks (2nd tried)

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

RE: Working with 2 workbooks (2nd tried)

(OP)
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,

RE: Working with 2 workbooks (2nd tried)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources