Macro Formula - Referencing by Declarations?!?!

[ClimoC]

Hello,

I have this piece of code doing a sort of If/Lookup function between two workbooks.

Sub boomerang()
Dim wb1 As Workbook
Dim master As Workbook
Dim sourcer As Range
Dim destr1, destr2, destr3, destr4 As Range
Dim c As Range
Dim r As Range
Dim ssheet As Worksheet
 
Set wb1 = ThisWorkbook
wb1.Activate
Set ssheet = ActiveSheet
    Set master = Workbooks("test.xls")
    If Err.Number > 0 Then Set master = Workbooks.Open(Filename:="Y:\DirPath\test.xls")
    If Not master Is Nothing Then master.Worksheets("Schedule").Activate Else MsgBox "File not found", vbInformation
master.Activate
Sheets("Schedule").Activate
Set destr1 = Range("AA6:AA500")
For Each c In destr1
        Cell.FormulaR1C1 = _
        "=IF(ISERROR(MATCH(1,(Book1.xls!$E$6:$E$100=I19)*(Book1.xls!$I$6:$I$100=N19)*(Book1.xls!$F$6:$F$100=J19),0)),"",INDEX(Book1.xls!$O$6:$O$100,MATCH(1,(Book1.xls!$E$6:$E$100=I19)*(Book1.xls!$I$6:$I$100=N19)*(Book1.xls!$F$6:$F$100=J19),0)))"
    Next
    
End Sub

For the bit in green , I need to be able to use the Declared workbooks "wb1" and "master" in this code. Reason being is that this module is exported to 15 different workbooks when they are created, so they all need to be able to figure out who they are, so that they may reference their own arrays instead of looking for "Book1.xls" which in all likelihood wouldn't exist.

Ps - it says "I19", "N19" because it's a sequential formula going in one whole column at the end of a table, and row 19 is just where I happened to copy the code from. Ideally, it should be looking at whatever row it happens to be in.

So can someone please quickly show me how to put in the wb1 and master in the formula so that in the reference are correct?

Cheers
C

 

[Joe4]

Duplicate:
http://www.mrexcel.com/forum/showthread.php?t=417673

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted:
http://www.mrexcel.com/forum/showthread.php?t=127080