Or you can try it like this using Formula instead of FormulaR1C1:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False

If Target.Address = "$A$1" Then
Cells(1, 2).Formula = _
"=VLOOKUP(A1,A3:B12,2,FALSE)"
ElseIf Target.Address = "$B$1" Then
Cells(1, 1).Formula = _
"=VLOOKUP(B1,B3:C12,2,FALSE)"
End If

ErrorHandler:
Application.EnableEvents = True 'Reset the events.
End Sub

Rowan

PS you may want to take the Debug.Print line out of the last example.

"filo666" wrote:

Hi, I want to do an easy thing, I have an inventory program with 10 codes in
one column(A3:A13) and 10 descriptions in the other (B3:B13), I want that the
user type a code in cell A1 and then in cell B1 appears the description, and
when the user type the description in cell B1 appears the code in A1, I have
the following proceedure :
Private Sub Worksheet_Change(ByVal Target As Range)
on error goto ErrorHandler
application.enableevents = false 'No events will fire
if target.address="$A$1" then
cells(1,2)= "=vlookup(A1;A3:B13;2;false)"
end if

if target.address="$B$1" then
cells(1,1)= "=vlookup(B1;A3:B13;1;false)"
end if

ErrorHandler:
application.enableevents = true 'Reset the events.
End Sub


It's not working, it's driving me crazy, I dont understand why??????

If I put the "=vlookup(B1;A3:B13;1;false)" without the "=" (like this
"vlookup(B1;A3:B13;1;false)") then appears in the cell this:
vlookup(B1;A3:B13;1;false)
so the Vlookup function is not evaluated.

Please HHHHHHHHEEEEEEEEELLLLLLLLLLLLLPPPPPPPPPPP

TIA