Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Hi guys,

Why is this not possible? I can extract a range from a closed workbook, etc. But apparently what I am asking for seems just too much for Excel to handle.

On Active Sheet Cell BF7 I have an Alpha/numeric value, which I want to use to Search Sheet2 of a closed workbook, locate the column with that value and then copy that column to Cell BE9 on the Active Sheet of the Active WorkBook.

Please do not laugh, but this is what I attempted so far without any joy:

Sub Select_BOM()
    Dim rBOM As Range
    Dim col As Long, rw As Long
    Dim ws As Worksheet
    Dim Directory As Range
    
    Set rBOM = Sheet2.Range("BF7")
      
    If Len(rBOM.Value) > 0 Then
        On Error Resume Next
    Directory = ThisWorkBook.path & "\Product B.O.M. DevG.xlsb\Sheet2"  ', i9:FB2009"
      With Directory
        col = Rows(9).Find(What:=rBOM.Value, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False, _
            SearchFormat:=False).Column
        rw = Columns("i").Find(What:=SubDiv, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False, _
            SearchFormat:=False).Row
        On Error GoTo 0
           
           
        If rw * col > 0 Then
            Application.Goto Reference:=Cells(rw, col)
            Cells(Rows.Count, Rng).End(xlUp).Row.Copy Destination:=ActiveSheet.Range("BE9")
        End If
      End With

    End If

End Sub

Is there anyone of you willing to fix this for me, please. Or can it not be done.

For security purposes we cannot consider opening the closed workbook.

Many Thanks

you can use in cell BE9

PHP Code: 

=INDEX('E:\OF\[adressen 002.xls]Blad1'!A9:BF9,Row(),Match($BF$7,'E:\OF\[adressen 002.xls]Blad1'!$A$9:$BF$9,0)) 


and autofill in column BF downwards

PS. adapt the file's name

sub snb()
 with sheets(1).cells(9,57).resize(2000)
   .formula="=INDEX('E:\OF\[adressen 002.xls]Blad1'!A9:BF9,Row(),Match($BF$7,'E:\OF\[adressen 002.xls]Blad1'!$A$9:$BF$9,0))" 
   .value=.value
  end with
End sub

I'd prefer a 'Stellenbos' claret