1

Recently I've found a very good script here on Stack Overflow. It works superbly, but I would like to adjust it a little bit - although my skill still doesn't let me to play with this kind of VBA very much. Until now, I've only failed in re-working this code.

My goal is to make this script do what it does, but from a fixed location - so I don't want to select it via a "question box" but copy a data range. For example: A1:A200 and paste it into another tab, like: DATA!A1:A200

Could you help me?

And the code:

Sub ListUniqueValues()

 'lists the unique values found in a user-defined range into a
 'user-defined columnar range

 Dim SearchRng     As Range
 Dim ResultRng     As Range
 Dim Cel          As Range
 Dim iRow          As Long

 Set SearchRng = Application.InputBox("Select search range", _
       "Find Unique Values", Type:=8)
 Do
    Set ResultRng = Application.InputBox("Select results columnar range", _
       "Write Unique Values", Type:=8)
 Loop Until ResultRng.Columns.Count = 1

 iRow = 0
 For Each Cel In SearchRng
    If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then
       'This value doesn't already exist
       iRow = iRow + 1
       If iRow > ResultRng.Rows.Count Then
         MsgBox "Not enough rows in result range to write all unique values", _
         vbwarning, "Run terminated"
         Exit Sub
       Else
         ResultRng(iRow).Value = Cel.Value
       End If
    End If
 Next Cel

 'sort result range
 'ResultRng.Sort ResultRng

End Sub
Community
  • 1
  • 1
Szymon Sid
  • 11
  • 3

1 Answers1

1

For your DATA!A1:A200 example change 

Set SearchRng = Application.InputBox("Select search range", _
   "Find Unique Values", Type:=8)

To 

Set SearchRange = Sheets("DATA").Range("A1:A200")

Edit

With all that being said haveyou seen this function

Dim SearchRng As Range, ResultRng As Range
Set SearchRng = Sheets("DATA").Range("A1:A200")
Set ResultRng = Sheets("Results").Range("A2")
SearchRng.AdvancedFilter Action:= xlFilterCopy, CopyToRange:=ResultRng, Unique:=True
Brad
  • 11,934
  • 4
  • 45
  • 73
  • Okay, it's a fixed Search location. Works fine. What about pasting results? Same idea, only to change the ResultRange to a fixed location? – Szymon Sid Nov 05 '12 at 17:56
  • Correct. And remove the Do While Loop too. – Brad Nov 05 '12 at 18:01
  • Could You, please, squeeze it into a full code? Thanks a million! – Szymon Sid Nov 05 '12 at 18:51
  • Okay, will look through it tomorrow at work where I have all the files, but I must tell You that I'm very new to all this VBA coding and with every hour spent on trying to do something with it the more I feel lost :-) But i can see the potential in it. I'll let You guys know how did it go, either way - thanks a lot! – Szymon Sid Nov 05 '12 at 19:00
  • It kicks out Error "91". Hints? :) – Szymon Sid Nov 05 '12 at 19:04
  • 1
    @SzymonSid Commonly an "Error 91 object variable or with block not set" occurs because the keyword `Set` is not used before setting an object variable equal to something. check out this post: http://stackoverflow.com/questions/5281759/simple-vba-code-gives-me-run-time-error-91-object-variable-or-with-block-not-set – Mike Kellogg Nov 05 '12 at 19:10
  • But are all of those lines of the code I've pasted exchangeable for only those few being shown by Brad? I don't ask You to give me a ready solution, only to lead me through it, please be patient :-) – Szymon Sid Nov 05 '12 at 19:16
  • No one's laughing. We all know how frustratingly cryptic VBA error messages can be. I did have a typo which I fixed (`SearchRange` should have been `SearchRng`). To help you on your end catch these kind of typos in the future you should always have `Option Explicit` as the very first line in any code module/class (check this box in the VBE editor > Tools > Options... > Editor > Require Variable Declaration) – Brad Nov 05 '12 at 19:29
  • And yes, those 4 lines of code should work as a full substitution for your code. :) – Brad Nov 05 '12 at 19:29
  • Omg. Works. And it's so simple in compare to what I was using. Genius! Thanks Brad! Any hints for a newbie in VBA? IS there any publication I should start my adventure with? – Szymon Sid Nov 05 '12 at 19:33
  • Macro recorder to discover the idea of how to do a task (but don't use the code generated from the Macro Recorder, it is often quite bad), fully qualified range references, option explicit. Those three things will get you a long way. SO + google is going to get you a lot further than a book on VBA I think. Good luck! – Brad Nov 05 '12 at 19:37