Mike
The code below should work for the 2 workbooks. It does assume that both are open. I've put some comments into the code so you can see what it is doing.
Sub aaa()
'make sure that you are in the workbook main
Workbooks("main.xls").Activate
'look at each entry in column C
For Each ce In Sheets("main sheet").Range("C2:C" & Sheets("main sheet").Range("C65536").End(xlUp).Row)
'determine if the trade reference in main.xls is found in [sent.xls]sent sheet
Set holder = Workbooks("sent.xls").Sheets("sent sheet").Range("B:B").Find(what:=ce.Value)
'Something is found
If Not holder Is Nothing Then
'check to see if the value in column P is prematched
If holder.Offset(0, 14).Value = "prematched" Then
'output the fixed values to columns Y:AC
ce.Offset(0, 22) = "MA"
ce.Offset(0, 23) = "SFT"
ce.Offset(0, 24) = "Trade matched (06/01)="
ce.Offset(0, 25) = "owenmi"
ce.Offset(0, 26) = Now()
End If
End If
Next ce
End Sub
For you second request change the line
If holder.Offset(0, 14).Value = "OK_PRE-MATCHED" Then
to
If holder.Offset(0, 2).Value = "OK_PRE-MATCHED" Then
The offset determines the number of columns to the right that the item is located. As the original was in Column P, it was 14 columns to the right of column B. As it is now in column D, it is only 2 columns to the right.
HTH
Tony