I’m not expert with vba codes and I have a workbook with 9 sheets have the same table design and I have got a vba code to copy the adjacent cell if the cell value equal any other cell in the range but it’s not working properly- my range is between columns “G:N” … in “G” I have “name” and in “H” I have the “phone number” and the same in the other coulmns as you can see in the screenshot
[
I need your help please to fix my code to to autofill the adjacent cell (phone number) if the “name” is repeated above in any cell of the range
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const START_ROW = 5
Const COL_NAME_FIRST = 7 ' Col G
Const CNT_NAME = 4
With Target
If .CountLarge > 1 Or .Row < START_ROW Or Len(.Cells(1).Value) = 0 Then Exit Sub
Dim rngName As Range, i As Long, rngCol As Range
Set rngName = Me.Columns(COL_NAME_FIRST)
For i = 1 To CNT_NAME - 1
Set rngName = Union(rngName, Me.Columns(COL_NAME_FIRST).Offset(, i * 2))
Next
If Application.Intersect(Target, rngName) Is Nothing Then Exit Sub
Set rngName = Intersect(rngName, Me.UsedRange)
For Each rngCol In rngName.Columns
Dim vRes: vRes = Application.Match(.Value, rngCol, 0)
If Not IsError(vRes) Then
Application.EnableEvents = False
.Offset(, 1) = rngCol.Offset(, 1).Cells(vRes)
Application.EnableEvents = True
Exit Sub
End If
Next
End With
End Sub