Need help simplifying VBA code

[jlclyde]

I am trying to go through a list after a new name has been added to
the bottom. If it matches any of the other names then it will exit.
If it does nto then it will copy it to D1. I knwo this works, but is
there an easier way to do this? I have several columns I need to
write this for and woudl prefer somethign easier.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Column = 1 Then
Range("A1").Select
Do Until Selection = Target.Row + 1
If Selection.Value = Target.Value Then
Exit Sub
Else
Selection.Offset(1, 0).Select
End If
If Selection.Row = Target.Row Then
Range("A" & Target.Row).Copy Destination:=Range("D1")
Exit Sub
End If
Loop
End If
End Sub

Thanks,
Jay

 

[Jim Thomlinson]

Try using countif...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column = 1 Then
Application.EnableEvents = False
Set rng = Range(Range("A1"), Target.Offset(-1, 0))
If Application.WorksheetFunction.CountIf(rng, Target.Value) = 0 Then _
Target.Copy Destination:=Range("D1")
Application.EnableEvents = True
End If
End Sub

 

[Duke Carey]

VBA code, generally speaking, is far more efficient when you don't SELECT
cells or ranges, especially in a loop.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
If Target.Column = 1 Then
x = Application.WorksheetFunction.CountIf(Range(Cells(1, Target.Column),
_Cells(Target.Row - 1, Target.Column)), Target.Value)
If x > 1 Then Range("D1") = Target.Value
End If
End Sub

 

[jlclyde]

VBA code, generally speaking, is far more efficient when you don't SELECT
cells or ranges, especially in a loop.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
If Target.Column = 1 Then
    x = Application.WorksheetFunction.CountIf(Range(Cells(1, Target..Column),  
         _Cells(Target.Row - 1, Target.Column)), Target.Value)
     If x > 1 Then Range("D1") = Target.Value
End If
End Sub







- Show quoted text -

Duke
Thanks for the code. This is going to work great. I am aware that
code runs faster without using the select, I just could not figure out
away around it without doing the Select.

Thanks Again,
Jay

 

[Joel]

try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.Column = 1 And Target.Row <> 1 Then
Set SearchRange = Range("A1:A" & (Target.Row - 1))
Set c = SearchRange.Find(what:=Target.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Range("D1").value = Target.Value
End If
End If
End Sub