×
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

Retreive data from different workbooks

Retreive data from different workbooks

Retreive data from different workbooks

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

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