Hello,
the code below is to search column 3 for any value put into column 1. then it is meant to look in column 4 and if that is empty paste in the information but if it has data in it to insert a line then paste the data. the issue I am having is that it inserts the line regardless of whether there is information or not. can someone tell me how to fix this and explain what I did wrong so that I don't make the same mistake in the future.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = Empty Then Exit Sub
Dim move As Range
Dim Info As Range
Dim full As Range
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
Application.EnableEvents = False
Set Info = Target
Set move = Columns(3).Find(Target.Value, , xlValues, xlWhole, searchdirection:=xlPrevious)
move.Select
ActiveCell.Offset(0, 1).Range("A1").Select
Set full = ActiveCell
If full Is Nothing Then
If Not move Is Nothing Then
move.EntireRow.Select
ActiveCell.Offset(0, 0).Range("A1").Select
Set move = ActiveCell
Info.Select
Selection.ClearContents
ActiveCell.Offset(0, 3).Range("A1:U1").Select
Selection.Copy
move.Select
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Else
MsgBox "Location not found", vbInformation
End If
Else
If Not move Is Nothing Then
move.EntireRow.Select
Selection.insert Shift:=xlDown
ActiveCell.Offset(0, 0).Range("A1").Select
Set move = ActiveCell
Info.Select
Selection.ClearContents
ActiveCell.Offset(0, 3).Range("A1:U1").Select
Selection.Copy
move.Select
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Else
MsgBox "Location not found", vbInformation
End If
End If
End If
full.Select
Application.EnableEvents = True
End Sub
apologies if the code is messy or inefficient, I am quite new to coding with VBA,
Bookmarks