The following works fine (with the help of the newsgroup I have been able to
modify some code)
If Not IsError(cyclookupValueB) And _
cell.Offset(0, 1) = "BUY" And _
cell.Offset(0, 3) = "INV" Then
cell.Offset(0, 6) = cyclookupValueB
ElseIf Not IsError(cyclookupValueS) And _
cell.Offset(0, 1) = "SAL" And _
cell.Offset(0, 3) = "CNR" Then
cell.Offset(0, 6) = cyclookupValueS

with these variables

cyclookupValueB = _
Application.VLookup(cell.Value, cycRange, 2, False)
cyclookupValueS = _
Application.VLookup(cell.Value, cycRange, 3, False)

not sure if the below is possible, however if I include the following

ElseIf Not IsError(cyclookupValueB) And _
cell.Offset(0, 1) = "BUY" And _
cell.Offset(0, 6) = cyclookupValueM Then
cell.Offset(0, 6) = cyclookupValueB
ElseIf Not IsError(cyclookupValueS) And _
cell.Offset(0, 1) = "SAL" And _
cell.Offset(0, 6) = cyclookupValueM Then
cell.Offset(0, 6) = cyclookupValueS

with the following variable
cyclookupValueM = _
Application.VLookup(cell.Value, cycRange, 6, False)

I am getting error 2042. Any suggesstions/assistance

--
thanks as always for the help