I Think I Need A Change Event Code

[Dazzawm]

Hi I have a list of numbers in column A & B on sheet 2. When I enter one of those numbers that are in column A into column AD on sheet 1 I would like the number next to it in column B on sheet 2 put in the cell directly above please. Thanks.

 

[Scott T]

Try

This code will check if something is in the cell and will NOT overwrite if there is something in the cell

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lr As Long

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
If Target.Column = 30 And Target.Row <> 1 And ws.Range("AD" & Target.Row - 1) = "" And IsNumeric(Target) Then
    mynum = Application.VLookup(Target, ws2.Range("A1:B" & lr), 2, 0)
    ws.Range("AD" & Target.Row - 1) = mynum
End If
Application.EnableEvents = True
End Sub

 

[Dazzawm]

Thanks Scott, what needs changing if i want to override whats in the cell?

 

[Dazzawm]

I just tried it and nothing happened? Does the code need to be on sheet 1 or 2 as I have tried both.

Sorry I forgot to mention if it makes a difference it is not a number on its on they will be in a format of CU1234 or FPCU4321 etc.

 

[Scott T]

The code should be on the sheet where the change takes place. That is where you would be typing into column AD. Easy way is get to the sheet module is to right click on the tab and select view code.

Rather then throw up and error if the value can not be found in sheet2 the code will do nothing. If you want an message or something then that could be added.


try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lr As Long

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set ws = Sheets("[COLOR=#FF0000]Sheet1[/COLOR]")  [COLOR=#008000]'Change sheet names to match yours[/COLOR]
Set ws2 = Sheets("[COLOR=#FF0000]Sheet2[/COLOR]") [COLOR=#008000]'Change sheet names to match yours[/COLOR]
lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
If Target.Column = 30 And Target.Row <> 1 Then
    mynum = Application.VLookup(Target, ws2.Range("A1:B" & lr), 2, 0)
    If Not IsError(mynum) Then ws.Range("AD" & Target.Row - 1) = mynum
End If
Application.EnableEvents = True
End Sub

 

[Dazzawm]

Thanks, works great now. My fault for letting you believe it was a number I guess.

Also what tweak needs to be made to change the cell below rather than above please?

 

[Dazzawm]

I figured out how to do the cell below. Thanks.