Changing VBA code (range to text value)?

[NumberCrunchingMonky]

i'm looking for some VBA code (rather, I found what looks like a pretty good starting point that needs some tweaking) that will return unique values from a list of duplicate values. Here's my example...

say, my lookup value is "xxxxx". I want to put "xxxxx" in the code to pull out all unique names associated with "xxxxx" in my data field (where "data field" is on sheet2 and I want the names returned on sheet1. The names adjacent to the codes are in a range named "Names".

Here's the code I found on this site thanks to Skp Vought:

sheet object code:
Private Sub Worksheet_Change(ByVal Target As Range)
MakeSelection Target
End Sub

module code:
Sub MakeSelection(Target As Range)
Set rng = Application.Intersect(Target, Range("StateInput&quot)
If Not rng Is Nothing Then
Sheets("Sheet1&quot.Cells(1, 1).CurrentRegion.Clear
With Sheets("Sheet4&quot.Cells(1, 1)
.AutoFilter Field:=1, Criteria1:=Target.Value
.CurrentRegion.Copy Destination:=Sheets("Sheet1&quot.Cells(1, 1)
End With
End If
End Sub


thank you.
monky

 

[SkipVought]

Hi,

How about a VLOOKUP in your code?

dim vName as Variant
vName = Application.VLookup("xxxxx", Sheet2.Range("DataTable"),2,False)
if not iserror(vName) then
' found a lookup value
...
end if

where DataTable is a range on sheet2 where column 1 has lookup values, xxxxx, and column 2 has Names.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884