Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help simplifying VBA code
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help simplifying VBA code
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 -- HTH... Jim Thomlinson "jlclyde" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help simplifying VBA code
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help simplifying VBA code
On Sep 4, 10:07*am, Duke Carey
wrote: 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" wrote: 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- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help simplifying VBA code
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 "jlclyde" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code needs simplifying | Excel Worksheet Functions | |||
Simplifying VBA code | Excel Worksheet Functions | |||
simplifying routine | New Users to Excel | |||
Simplifying a formula | Excel Worksheet Functions | |||
Simplifying formula | Excel Discussion (Misc queries) |