Take values from an excel spreadsheet and display in a pop-up

Question

I’m developing a spreadsheet and I’m having a hard time starting to work with the macros/vba. However I could not find a tutorial that showed 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 called "Today" that when clicked it through the function today() would check the table if any of the date columns contains today’s date and show the names in a pop-up. For example, assuming today is 02/01, when clicking the button would open a pop-up written: Maria and Marco.

Is this possible in Excel? Thank you very much.

Answer 1

Good afternoon, my friend, I don’t know for what purpose you will use the method, but a simple way to solve what you are trying to do is to scroll through the two columns of dates you created and assign the names found to a string. Of course, depending on your business rule, this may vary. Follow 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

Answer 2

Code

There are several ways to match the values of 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 autofilter is also fast and easier to understand. Therefore, this will be used. The interaction between the VBA and the spreadsheet must be minimized and this reference hard-working.

Statements

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

Leading

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

Dice

Assuming the values as follows:

If you are from another, format in the spreadsheet or code.

Upshot