Get values from an excel sheet and display in a popup

2

I'm developing a spreadsheet and I'm having trouble getting started with macros / vba. But I could not find a tutorial that would show me how to do what I need.

I have a table that says:

nome | data1 | data2

Maria | 01-01 | 02-01

Marco |  02-01 | 03-01

I wanted to create a button named "Today" which when clicked through the function today () would check in the table if any of the date columns contains today's date and would show the names in a popup. For example, assuming that today is 1/2, clicking the button would open a pop-up written: Maria and Marco.

Is this possible in Excel? Thank you very much.

    
asked by anonymous 13.11.2017 / 16:50

2 answers

2

Good afternoon, my friend, I do not know for what purpose you will use the method, but a simple way to solve what you are trying to do is to traverse the two columns of dates you have created and assign a string to the names found. Of course, depending on your business rule, this can vary. Here is an example of the method:

Public Sub Pesquisa_Hoje()

Hoje = Format(Now, "DD/MM/YYYY")
Dim Achou As String
Dim Data As String

'Faz a busca na primeira coluna de datas
Fim = Worksheets("Planilha1").Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To Fim
Data = Range("B" & i).Value

If Data = Hoje Then

    If Achou = "" Then
        Achou = Range("A" & i).Value
    Else
        Achou = Achou & ", " & Range("A" & i).Value
    End If

End If

Next

'Faz a busca novamente na segunda coluna de datas
Fim = Worksheets("Planilha1").Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To Fim
Data = Range("C" & i).Value
If Data = Hoje Then

    If Achou = "" Then
        Achou = Range("A" & i).Value
    Else
        Achou = Achou & ", " & Range("A" & i).Value
    End If

End If

Next

'Mostra os nomes encontrados
If Achou <> "" Then

    MsgBox (Achou)

    Else

    MsgBox ("Nenhum nome encontrado!")

End If

End Sub
    
13.11.2017 / 18:36
1

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

    
13.11.2017 / 19:59