Quick find in a sheet and table population

[mmf]

Hi,
I am triying to get this sorted out, but I couldn't
I have two sheets, one is a main sheet where a table has to be populated with values according to a date changed by the user.
in the second sheet I have the database from where I would like the date to be retrieved

I made a peace of code, but is too slow

and also would be nice that when a value is not found set the cell in blank.
and the table get updated without click the commnadbutton when the date is changed.

Any help with this will be welcomed
I attached the file so you can see my mess
cheers

[Leith Ross]

Hello mmf,

I added the macro below to your workbook, which is attached to this post. It will run automatically when the value in cell "B1" on Main is changed. The command button will also call the same macro. As you can see, this macro is quite a bit more complex than yours. It takes advantage of the Auto-Filter function to sort the data by date.

Sub UpdateValues2()

  Dim A As Long
  Dim DBRng As Range
  Dim DBWks As Worksheet
  Dim FilterRng As Range
  Dim LastRow As Long
  Dim MainRng As Range
  Dim MainWks As Worksheet
  Dim StartRow As Long
  Dim X As Range
  
    StartRow = 2
    Set DBWks = Worksheets("DB")
    Set MainWks = Worksheets("Main")
    Set MainRng = MainWks.Range("$C$5:$E$8")
      
     'Find the last row and display the all the data on DB sheet
      With DBWks
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
        Set DBRng = .Range(.Cells(StartRow, "A"), .Cells(LastRow, "E"))
        If .FilterMode = True Then .ShowAllData
      End With
      
     'Filter DB sheet by date in "B1" of Main sheet
      DBRng.AutoFilter Field:=2, Criteria1:=MainWks.Range("$B$1").Text, VisibleDropDown:=True
      
     'Check for filtered data in the list
      Set DBRng = DBRng.SpecialCells(xlCellTypeVisible)
        If DBRng.Areas.Count = 1 Then Exit Sub
      
     'Initialize the range object variable FilterRng
      Set X = DBRng.Areas(2)
      Set FilterRng = X.Range(Cells(1, 3), Cells(1, 5))
      
       'Collect only cells in columns "C:E" of the filtered rows
        For A = 2 To DBRng.Areas.Count
         Set X = DBRng.Areas(A)
         Set FilterRng = Union(FilterRng, X.Range(Cells(1, 3), Cells(1, 5)))
        Next A
      
     'Clear any previous data
      MainRng.ClearContents
     'Copy the filtered data to cells "$C$5:$E$8" the Main sheet
      FilterRng.Copy Destination:=MainWks.Range("$C$5")
      
End Sub

[mmf]

Many thanks for the code I really appreciate the time.
here in my computer is not working, I was trying to figure out why it is.
the autofilter is activate, but do not show any values, so the code can not retrieve any data.
any clue why that is happening
cheers

[Leith Ross]

Hello mmf,

Can you post your workbook? I have a look at what's going on. The code should prevent that condition from happening.

[mmf]

Sorry leith, to do not answer early, but god last weeks have been very hetic.
Please find the workbook that is not working
Best Regards
MM

[Leith Ross]

Hello mmf,

The data may not be visible if the auto filter has failed to find a match. On the menu bar, click b]Data, Filter, Show All[/b]. This will restore the list. The macro does this automatically each time it runs, but leaves the filtered results.

Sincerely,
Leith Ross