Code
There are several ways to match the values in an Excel file. (.Find, Match, Arrays, Dictionary, Collection, Autofilter, Loop, Excel Formula)
If the spreadsheet has a lot of data, the methods that use arrays are faster, but the auto-filter is also quick and easy to understand. Therefore, this will be used. The interaction between the VBA and the worksheet should be minimized and this reference read.
Declarations
Dim ws As Worksheet
'Worksheet de index 1 ou inserir o "NOME_entre_aspas"
Set ws = ThisWorkbook.Sheets(1)
Dim j As Long, i As Long, LastRow As Long
Dim VisibleRange As Range
Dim MyArray As Variant
ReDim MyArray(0 To 1000)
j = 0
Main
Data = Format(Now, "dd-mm")
'Loop da Coluna 2 (B) até C(3)
With ws
'Última Linha da coluna A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To 3
'AutoFiltro
.Range(.Cells(1, i), .Cells(1, i)).AutoFilter Field:=i, Criteria1:=Data
'Cria range com as células visíveis após Filtrar
On Error Resume Next
Set VisibleRange = .Range(.Cells(2, 1), .Cells(LastRow, 1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not VisibleRange Is Nothing Then
'Loop nas células visíveis após aplicação do AutoFiltro
For Each Cell In VisibleRange
MyArray(j) = Cell
j = j + 1
Next
End If
'"zera" o autofiltro, mostrando todos dados filtrados
If .FilterMode Then
.ShowAllData
End If
Next i
'Redimensiona Array para quantidade de elementos correto
If j > 0 Then
ReDim Preserve MyArray(0 To j - 1)
'Loop em cada elemento da Array para criar String
For j = LBound(MyArray) To UBound(MyArray)
txt = txt & MyArray(j) & vbCrLf
Next j
MsgBox txt
Else
MsgBox "Nenhuma data encontrada."
End If
End With
Data
Assuming the values as follows:
Ifitisotherwise,formatitinthespreadsheetorinthecode.
Result