# Using VBA to identify if name column value has been entered previously, then copy value from phone number column of previous name row



## tbxor (Dec 14, 2022)

I want the value of phone number of Guest Name to be automatically entered using VBA. In this case, it's for John.

Guest Badge Registration Echo Post.xlsmEF1Guest NameContact Number2John01223456783Doe01456789124Isaac01567899235Tress04568971326Lucy03565754567JohnJanuary23


----------



## Sahak (Dec 15, 2022)

Hi,
Try this code in the worksheet module:

```
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    On Error Resume Next
    Dim MatchGuest As Long
    MatchGuest = WorksheetFunction.Match(Target, Range("A:A"), 0)
    If MatchGuest = Target.Row Then Exit Sub
    Target.Offset(0, 1).Value = Range("B1").Offset(MatchGuest - 1).Value
End Sub
```


----------



## tbxor (Dec 15, 2022)

Sahak said:


> Hi,
> Try this code in the worksheet module:
> 
> ```
> ...


this didn't work. no change at F7


----------



## tbxor (Dec 15, 2022)

tbxor said:


> this didn't work. no change at F7


I used this

```
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    On Error Resume Next
    Dim MatchGuest As Long
    MatchGuest = WorksheetFunction.Match(Target, Range("E:E"), 0)
    If MatchGuest = Target.Row Then Exit Sub
    Target.Offset(0, 1).Value = Range("F1").Offset(MatchGuest - 1).Value
End Sub
```


----------



## Sahak (Dec 15, 2022)

You did good, my macro was for columns *A* & *B *


----------



## tbxor (Dec 15, 2022)

Sahak said:


> You did good, my macro was for columns *A* & *B *


I see. I forgot to replace the if condition to be for the fifth column. Working now but I need to match the most recent value, the lower rows since some of them changed their phone numbers. Is there a way to reverse the lookup flow?


----------



## Peter_SSs (Dec 15, 2022)

Try this


```
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Target.Column = 5 And Target.Row > 1 Then
    Application.EnableEvents = False
    With Target
      .Offset(, 1).Value = Evaluate(Replace(Replace("xlookup(""" & .Value & """,#,%,"""",,-1)", "#", _
        .EntireColumn.Resize(.Row - 1).Address), "%", .Offset(, 1).EntireColumn.Resize(.Row - 1).Address))
    End With
    Application.EnableEvents = True
  End If
End Sub
```


----------



## tbxor (Dec 16, 2022)

Peter_SSs said:


> Try this
> 
> 
> ```
> ...


I'd send thousands like if I could. Both of you guys are awesome! Thanks!


----------



## Peter_SSs (Dec 16, 2022)

You're welcome. Glad we could help. Thanks for the follow-up.


----------



## Sahak (Dec 16, 2022)




----------



## tbxor (Dec 14, 2022)

I want the value of phone number of Guest Name to be automatically entered using VBA. In this case, it's for John.

Guest Badge Registration Echo Post.xlsmEF1Guest NameContact Number2John01223456783Doe01456789124Isaac01567899235Tress04568971326Lucy03565754567JohnJanuary23


----------



## tbxor (Dec 19, 2022)

Peter_SSs said:


> Try this
> 
> 
> ```
> ...


with this code, the F column value will be erased once I edited the E column. How do I set a condition: if column F data not blank, then do nothing


----------



## Peter_SSs (Dec 19, 2022)

tbxor said:


> with this code, the F column value will be erased once I edited the E column. How do I set a condition: if column F data not blank, then do nothing


It is unclear to me exactly what your are doing and what should be happening.
What editing are you doing in column e?


----------



## tbxor (Dec 19, 2022)

Peter_SSs said:


> It is unclear to me exactly what your are doing and what should be happening.
> What editing are you doing in column e?


The name. Sometime I got a typo in their name in column E and when I fix that typo, the number that I've entered for that row in column F got cleared since there's no previous record of their name in the worksheet


----------



## Peter_SSs (Dec 19, 2022)

We can do that (code below) but are you *sure* that is what you want?
For example, if the data is as I have shown here and you go to type "John" in E6 but accidentally type "Jon" instead and then correct "Jon" to "John" you will be left with "06666666" in F6 which is an incorrect number for John.

tbxor.xlsmEF1NameNumber2John012356983Doe012356994Isaac012357005Jon066666666Sheet1


```
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Target.Column = 5 And Target.Row > 1 Then
    If IsEmpty(Target.Offset(, 1).Value) Then
      Application.EnableEvents = False
      With Target
        .Offset(, 1).Value = Evaluate(Replace(Replace("xlookup(""" & .Value & """,#,%,"""",,-1)", "#", _
          .EntireColumn.Resize(.Row - 1).Address), "%", .Offset(, 1).EntireColumn.Resize(.Row - 1).Address))
      End With
      Application.EnableEvents = True
    End If
  End If
End Sub
```


----------



## tbxor (Dec 20, 2022)

Peter_SSs said:


> We can do that (code below) but are you *sure* that is what you want?
> For example, if the data is as I have shown here and you go to type "John" in E6 but accidentally type "Jon" instead and then correct "Jon" to "John" you will be left with "06666666" in F6 which is an incorrect number for John.
> 
> tbxor.xlsmEF1NameNumber2John012356983Doe012356994Isaac012357005Jon066666666Sheet1
> ...


Yep. This will do. I'll just clear the number and edit name cell to trigger the correct number lookup


----------



## Peter_SSs (Dec 20, 2022)

OK, if that's what you want. 
Thanks for letting us know.


----------

