why a workcheet_change function is not working???

[Guest]

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

 

[Guest]

The line cells(1,2)= "=vlookup(A1;A3:B13;2;false)" results in an error which
means your macro skips to the error handler. Try the code below but you will
have to repeat the inventory codes in column C - vlookups only work left to
right (I think). So you data would look like:

Code Description Code
1098 Something 1098
1785 Another 1785


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

Debug.Print Target.Address

If Target.Address = "$A$1" Then
Cells(1, 2).FormulaR1C1 = _
"=VLOOKUP(RC[-1],R[2]C[-1]:R[11]C,2,FALSE)"
ElseIf Target.Address = "$B$1" Then
Cells(1, 1).FormulaR1C1 = _
"=VLOOKUP(RC[1],R[2]C[1]:R[11]C[2],2,FALSE)"
End If

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

HTH
Rowan

 

[Guest]

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.

 

[Tim Zych]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Cells(1, 2).Formula = "=vlookup(A1,A3:B13,2,false)"
Application.EnableEvents = True
ElseIf Target.Address = "$B$1" Then
Application.EnableEvents = False
Cells(1, 1).Formula = "=vlookup(B1,A3:B13,1,false)"
Application.EnableEvents = True
End If
End Sub

 

[Guest]

The mechanics of this option will work but the second vlookup will return a
#NA error as the value in B1 will not be found in column A.

Regards
Rowan

 

[Guest]

Thanks you all, your information was very helpfull