Lookup formula in VBA to another workbook

[Blunder1]

Hi,
I have the below code which i'm struggling to change the look formula so i dont have to change the workbook names. The first Set named workbook 'ra' has to lookup to the second named workbook 'sa'.

For the below:-
workbook ra = OSD NTPA rec COB 10 Feb 2011.xls
workbook sa = OSD NTPA rec COB 9 Feb 2011.xls

Sub Macro1()
Dim ra, sa As Workbook
Dim rb, rc, sc, sb As Worksheet
Dim x As String
Application.ScreenUpdating = False
Set ra = Application.ActiveWorkbook 'OSD NTPA rec COB 10 Feb 2011.xls
Set rb = Sheets(2)
Set rc = Sheets(3)
lrrb = rb.Range("a65536").End(xlUp).Row
lrrc = rc.Range("a65536").End(xlUp).Row
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then x = wb.Name
Next wb
Workbooks(x).Activate
Set sa = Application.ActiveWorkbook
Set sb = Sheets(2)
Set sc = Sheets(3)

ra.Activate
rb.Select
rb.Range("al2:" & "al" & lrrb).ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-37],'[OSD NTPA rec COB 9 Feb 2011.xls]ODS'!C1:C39,38,0))=false,1,VLOOKUP(RC[-37],'[OSD NTPA rec COB 9 Feb 2011.xls]ODS'!C1:C38,38,0)+1)"
 

End Sub

I was thinking of replacing teh workbook names with something like 'sa.sheets(2) 'but this doesn't seen to work in the formula.

I have quite a few to do as i'm aging items so i just want two open workbooks at a time then run the macro.

Thanks in advance

Blunder

 

[venkat1926]

I wonder whether I have completely understood your problem

But I suggest you move the code statement

next wb

to the last before end sub

 

[Blunder1]

thanks, but not quite what i'm looking for.

The lookup formula has the exact name of the workbook it's looking up from. This will change each day so i wanted to look up from the other open active workbook. I can select and name this ( in this case named 'sa') but i cant use that withing the formula

Thanks