Need help simplifying VBA code

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

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
 
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
 
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
 
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 -
Click to expand...

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
 
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
 
Back
Top