| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why a workcheet_change function is not working???
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
why a workcheet_change function is not working???
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 "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
why a workcheet_change function is not working???
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
why a workcheet_change function is not working???
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 "filo666" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
why a workcheet_change function is not working???
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 "Tim Zych" wrote: 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 "filo666" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
why a workcheet_change function is not working???
Thanks you all, your information was very helpfull
"Rowan" wrote: 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 "Tim Zych" wrote: 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 "filo666" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
| Old function is not working. | Excel Worksheet Functions | |||
| IF(AND function is not working | Excel Worksheet Functions | |||
| Function not working | Excel Worksheet Functions | |||
| REQ: I need help working out this function. | Excel Programming | |||
Linear Mode
