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 |
|
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 |
|