Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / October 2008

Tip: Looking for answers? Try searching our database.

Change criteria for worksheet change code.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
J.W. Aldridge - 11 Oct 2008 16:16 GMT
Code works to :
List all unique values from column A in Column B, starting with row
12.

I need to know how to change the criteria in the code to do the
following:

List all unique values from column L, in column Q, starting with row
12.

(I tried changing the obvious letters.... but must be a little more to
it!).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
  rw = Range("B65536").End(xlUp)(2).Row
  If rw < 12 Then rw = 12
  Cells(rw, 2).Value = Target.Value
End If
For myRow = Range("B65536").End(xlUp).Row To 12 Step -1
  If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
      Cells(myRow, 2).ClearContents
  End If
Next myRow
Application.EnableEvents = True
End Sub
Don Guillett - 11 Oct 2008 16:28 GMT
Why not just use
data>filter>advanced filter>unique values>>>>

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Code works to :
> List all unique values from column A in Column B, starting with row
[quoted text clipped - 27 lines]
> Application.EnableEvents = True
> End Sub
J.W. Aldridge - 11 Oct 2008 16:45 GMT
Need to automate this process which affects another part of
complicated worksheet....

Wont be able to stop and run filter etc.

Code works fine, just need to know what parts are to be altered in
order to change criteria to suite.

Thanx
Don Guillett - 11 Oct 2008 17:30 GMT
Record a macro while doing and clean up or post your code afterwards for
comments

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Need to automate this process which affects another part of
> complicated worksheet....
[quoted text clipped - 5 lines]
>
> Thanx
J.W. Aldridge - 11 Oct 2008 18:19 GMT
Ok....

Tried that.

Macro does...(copy the list, create another page, paste it there,
filter it, bring it back to the original page to the desired cell,
then delete the temp page.)
Don Guillett - 11 Oct 2008 22:04 GMT
Post your code for comments and/or send your workbook to my address below

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Ok....
>
[quoted text clipped - 3 lines]
> filter it, bring it back to the original page to the desired cell,
> then delete the temp page.)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.