Getting a cell value based on a pre-existing cell value dynamically

[abdelfattah]

hi, experts
i write data manually in column a, b what i want if i write duplicated data in column a then bring value in column b for instance i write theses data

a b
aa 123
bb 1234
cc 12345
then if i repeat this
aa should bring 123
if is possible do that by macro in worksheet event change
thanks advance

 

[Joe4]

Are you going to always be entering data in column A down the column, so that you would only ever need to look at the rows above the one you just entered?

 

[Joe4]

If my above assumption is correct, try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim rng As Range
    Dim val As String

'   Exit if multiple cells updated at once (i.e. Copy/Paste)
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if entry not in column A or in row 1 or 2
    If (Target.Column > 1) Or (Target.Row <= 2) Then Exit Sub
    
'   Exit if column A empty
    If Target.Value = "" Then Exit Sub
    
'   Build range to lookup
    r = Target.Row
    Set rng = Range("A2:B" & r - 1)
    
'   Perform vlookup on entry
    On Error GoTo err_exit
    val = Application.WorksheetFunction.VLookup(Target.Value, rng, 2, 0)
    
'   Put looked up value in column B, if column B is blank
    If Cells(r, "B") = "" Then Cells(r, "B") = val

    Exit Sub
    
err_exit:
    Err.Clear

End Sub

The code could be shortened, but I wanted to make it clear what each part is doing.

 

[abdelfattah]

thanks joe for this code works excellently but i have problem when i add a new data not duplicated it gives me error

1004 Error with WorksheetFunction.VLookup?​

in this line

val = Application.WorksheetFunction.VLookup(Target.Value, rng, 2, 0)

 

[Joe4]

thanks joe for this code works excellently but i have problem when i add a new data not duplicated it gives me error

1004 Error with WorksheetFunction.VLookup?​

When you enter formulas into Excel, do you use commas or semi-colons as argument separators?
If semi-colons, you may need to change that formula to:

val = Application.WorksheetFunction.VLookup(Target.Value; rng; 2; 0)

 

[abdelfattah]

yes when i use formula it is semi-colons despite for this it gives me compile error

 

[Joe4]

Go into the VB Editor, and from the Tools menu, select References and see if a library that looks like the following is selected:
Microsoft Excel 16.0 Object Library

If it is, can you post your actual data?

 

[abdelfattah]

yes it is
and this is my data

1 Microsoft Excel جديد (2).xlsx
	A	B
1	item	val
2	aa	123
3	bb	1234
4	cc	12
5	dd	89
6	ee	76
sheet1

 

[Fluff]

If it works with duplicated values, try this mod to Joe's code

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim rng As Range
    Dim val As Variant

'   Exit if multiple cells updated at once (i.e. Copy/Paste)
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if entry not in column A or in row 1 or 2
    If (Target.Column > 1) Or (Target.Row <= 2) Then Exit Sub
    
'   Exit if column A empty
    If Target.Value = "" Then Exit Sub
    
'   Build range to lookup
    r = Target.Row
    Set rng = Range("A2:B" & r - 1)
    
'   Perform vlookup on entry
    On Error GoTo err_exit
    val = Application.VLookup(Target.Value, rng, 2, 0)
    If IsError(val) then Exit Sub
'   Put looked up value in column B, if column B is blank
    If Cells(r, "B") = "" Then Cells(r, "B") = val

    Exit Sub
    
err_exit:
    Err.Clear

End Sub

 

[abdelfattah]

thanks fluff for mod the code and thanks joe for the code now it works as what i want