The vba code below changes the value in a cell based on a vlookup. What I am not able to do is instead of having to change the value in 4 cells, change it so that only one cell (1) is changed to 1,2,3 then the cells in D1 D2 D3 D4 E1 E2 E3 E4 are updated using the values from ID that matches the digit in column 1. Thank you
.
Named Range: ID
1 aaa 1,1
1a bbb 2,2
1b ccc 3,3
1c ddd 4,4
2 bbb 2,2
2a ccc 3,3
2b ddd 4,4
2c aaa 1,1
3 ccc 3,3
3a bbb 2.2
3b ddd 4,4
3c aaa 1,1
vba to change value in column 4 using 2 named range
Private Sub Worksheet_Change(ByVal Target As Range)
I = Target.Value
If Target.Column = 4 Then
s = Application.VLookup(I, ActiveSheet.Range("ID"), 2, False)
If Not IsError(spikein) Then
Target.Value = spikein
End If
End If
End Sub
vba to change value in column 5 using 3 in the named range
Private Sub Worksheet_Change_2(ByVal Target As Range)
L = Target.Value
If Target.Column = 5 Then
sl = Application.VLookup(Location, ActiveSheet.Range("ID"), 3, False)
If Not IsError(spikeinloc) Then
Target.Value = spikeinloc
End If
End If
End Sub
desired
c1 D E
1 aaa 1,1
bbb 2,2
ccc 3,3
ddd 4,4
Bookmarks