Get field value on pressing enter at cell in excel

alpha · #1 01-20-2019, 06:18 AM

If your codes and names are on sheet2 in columns A and B, beginning in row 2:
- Copy the code below.
- Rightclick on the sheettab of sheet1 and click "View code".

- Where the cursor is flashing, rightclick and choose "Paste".
- Now you can drop codes in Sheet1 columnB, beginning in B2.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Integer
If Not Intersect(Range("b2:b30"), Target) Is Nothing Then
a = Application.CountIf(Sheets(2).Range("a2:a30"), Target)
If a > 0 Then
Target.Value = Application.VLookup(Target, Sheets(2).Range("a2:b30"), 2, 0)
End If
End If
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula to enter value based on value of another cell	onemorething	Excel	3	12-06-2017 09:26 AM
MS Word Horizontal Line Disappears after pressing Enter from end of line	MikeWhite	Word	5	01-20-2017 03:39 PM
Addresses in To... field disappear when pressing send	BigFinger	Outlook	0	02-26-2015 06:01 AM
default cursor movement after pressing enter, ms office 2013, win 8	sansousea	Excel	0	07-11-2014 09:46 AM
how to enter what i want in cell	tasuooooo	Excel	4	07-23-2012 05:45 AM