Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat.
Nou pe simpatie:
Alexanndra1994 pe Simpatie.ro
Femeie
24 ani
Bucuresti
cauta Barbat
23 - 42 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Search For Multiple Values from Range in Another Range [VBA] Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
TRaP
Moderator

Inregistrat: acum 6 ani
Postari: 732
I need to search in an excel column for 60 values existing in a range.
The column I need to search values in is located in sheet TOATE, column 16 (P).
The range containing the values I need to search is located in Sheet1, range C2:C60.

The code below will color in yellow the cell from sheet TOATE if it contains one of the values from Sheet1.Range("C2:C60").



Sub SearchArray_inRange()

Application.ScreenUpdating = False

Dim MyAr As Variant
Dim ws, wsAr As Worksheet
Dim aCell As Range, bCell As Range
Dim i As Long

Set ws = ActiveWorkbook.Sheets("TOATE")
Set wsAr = ActiveWorkbook.Sheets("Sheet1")

MyAr = wsAr.Range("C2:C60").Value2

With ws
    '~~> Loop through the array
    For i = LBound(MyAr) To UBound(MyAr)
        Set aCell = .Columns(16).Find(What:=MyAr(i, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            Set bCell = aCell
            aCell.Interior.ColorIndex = 6

            Do
                Set aCell = .Columns(16).FindNext(After:=aCell)

                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do
                    aCell.Interior.ColorIndex = 6
                Else
                    Exit Do
                End If
            Loop
        End If
    Next
End With

Application.ScreenUpdating = True

End Sub


Source for the search code (I had to modify it a bit and make the array as a range instead of a manual array):

Source on how to extract the array values from a range:


pus acum 3 ani
   
Pagini: 1  

Mergi la