Macro to search range for value, and copy first instance if found. Then search for others.

SensualCarrots

New Member
Joined
Mar 21, 2015
Messages
46
I'm looking for a macro to do the following:
Column A is given data.
Column B is what I want to happen when the macro is executed.

AA
AB
AC
CD
B
B
C
C
D
A

<tbody>
</tbody>


I would imagine it will just be a copy and paste of the code, modifying the search parameters. I can handle that part. Just need the base code to find the data and return the first found value.

Curve Ball:

Sometimes my cells will contain part of the same string of an entire other cell. For example, one cell in my search range may be 1A, and another may be 1A.1 I need to make sure it is matched to the whole word, as searching for 1A may result in true if 1A.1 is present, but 1A is not. Thanks in advance for your help!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Do you want col B to be a list of all unique values in col A?
 
Upvote 0
How about
Code:
Sub getuniques()
   Dim cl As Range
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(cl.Value) Then .Add cl.Value, Nothing
      Next cl
      Range("B1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub
 
Upvote 0
This works perfect. Thanks! What I'm trying to do now is have the data appear in a list. I have set up a data validation list on another page, but the problems is that the above code could produce anywhere from 5-50 cells worth of data. I can achieve my goal by just selecting the 50 possible cells that the data will appear in, but then I have lots of blanks in my list. Not the end of the world, but still annoying. I've searched the web, and it doesn't appear there is an easy way to have a data validation list omit blank cells from its source information. Is there a way to have this produce a dynamic data validation list, or something of the sort? What I'm trying to do now is give the user the ability to select which items from the list they want to use as reference in the previous code you wrote for me. I can then change the reference to what is selected from the list that is populated from the above code. Thanks again Fluff, you seriously are a genius!
 
Upvote 0
You can use this
Code:
Sub getuniques()
   Dim cl As Range
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(cl.Value) Then .Add cl.Value, Nothing
      Next cl
         Range("B1").Resize(.Count).Value = Application.Transpose(.keys)
         Range("B1").Resize(.Count).Name = "[COLOR=#ff0000]Lst[/COLOR]"
   End With
End Sub
Change the value in red to suit & then use that named range in your DV
 
Upvote 0
Ok that worked nicely. Now I'm trying to use that data in the earlier code you gave me. Here it is modified.

Code on Sheet "Export"
Code:
Private Sub ExportGetData_Click()
Answer = MsgBox("Are you sure? This will overwrite current data and cannot be undone.", vbYesNoCancel + vbInformation, "Application Message")
If Answer = vbYes Then Else Exit Sub
        Sheets("Export Hidden").Range("B3:H502").ClearContents
        Dim Ary As Variant
        Dim Nary() As Variant
        Dim i As Long, j As Long, k As Long
        Ary = Application.Transpose(Sheets("Cable Labels").Range("A2", Sheets("Cable Labels").Range("G" & Rows.Count).End(xlUp)))
        For i = 1 To UBound(Ary, 2)
            If InStr(1, Ary(2, i), Range("P2")) > 0 Then
                j = j + 1
                ReDim Preserve Nary(1 To UBound(Ary, 1), 1 To j)
                For k = 1 To UBound(Ary, 1)
                Nary(k, j) = Ary(k, i)
            Next k
            End If
            Next i
            On Error GoTo ErrorHandler
        Sheets("Export Hidden").Range("B3").Resize(j, UBound(Nary)).Value = Application.Transpose(Nary)
ErrorHandler:
MsgBox ("No Data Found."), , "Notice"
End Sub

My Data Validation List is on Sheet "Export" in Cell P2, and it is populating correctly. However, regardless of what is selected in the dropdown box, the above code pulls in everything from the array, not just the matching data. I've tried > 0 and > "" to no avail. I put the old value of "." in and it started working properly again, so it has to be something with referencing P2. I also changed the lookup cell in the array as I am referencing the 2nd column in the row, not the first. It seems to be working in that regard, but is that the correct way to do it?
 
Upvote 0
If you are looking for an exact match, use
Code:
   For i = 1 To UBound(Ary, 2)
      [COLOR=#ff0000]If Ary(2, i) = Range("P2") Then[/COLOR]
         j = j + 1
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top