• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

How do populate filtered rows in an Excel VBA application ?

Hi,

I have an Excel VBA workbook comprised of two sheets in which I am trying to populate a cell in sheet 1 with today's date, and then populate cells in a filtered list in sheet 2 with the same date.

The process is as follows:

1. In a table ("Invoices") in sheet 1, double-click on a cell in the DateInvoiced column.
2. Populate that selected cell with today's date.
3. Filter a table ("Sessions") in sheet 2 by the InvoiceNo of the row selected in sheet 1.
4. Populate the DateInvoiced cells of the filtered list ("Sessions") with today's date.


I've attached a cut-down version of the application.

Hope that makes sense
Thanks
TestBook27.xlsx
0
Tocogroup
Asked:
Tocogroup
  • 3
  • 3
1 Solution
 
ChloesDadCommented:
You need to save your file as a macro enabled workbook, then you can view macros, this code goes in sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

   Dim TargetValue As String
   Dim objListRows As ListRows

   
   If Target.Column = 4 Then
   
     Target.Value = Now()
   
   
     TargetValue = Cells(Target.Row, 2).Value
   
     Sheets("Sheet2").Select
   '  ActiveSheet.ListObjects("Sessions").Range.AutoFilter Field:=3, Criteria1:=TargetValue
     Set objListRows = ActiveSheet.ListObjects("Sessions").ListRows
     
     For Count1 = 1 To objListRows.Count
       
       Dim RowNumber As Integer
       
       RowNumber = objListRows(Count1).Range.Row
       
       If ActiveSheet.Cells(RowNumber, 4).Value = TargetValue Then
         ActiveSheet.Cells(RowNumber, 5) = Now()
       End If
     
     Next Count1
     
     

  End If
  

Open in new window


If you add the line back in that is commented out then the filter will be applied, but its not necessary for the macro to work
0
 
TocogroupAuthor Commented:
Excellent. Does exactly what I wanted. Many thanks for your help.
Toco
0
 
TocogroupAuthor Commented:
Sorry, I was rather too quick with my response. I've noticed the 'For Next' construct loops through the whole Sessions table, rather than just the filtered list.

How do I loop just through the rows I've filtered ?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ChloesDadCommented:
I couldn't work that out, that's why I commented out the filter. There doesn't appear to be a filtered property for the list object. I'll keep looking for you though.
0
 
ChloesDadCommented:
This will work, its a bit of a fudge as it applies a second filter to only list visible cells

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

   Dim TargetValue As String
   Dim objListRows As Range

   
   If Target.Column = 4 Then
   
     Target.Value = Now()
   
   
     TargetValue = Cells(Target.Row, 2).Value
   
     Sheets("Sheet2").Select
          
     ActiveSheet.ListObjects("Sessions").Range.AutoFilter Field:=3, Criteria1:=TargetValue
        
     Set objListRows = ActiveSheet.ListObjects("Sessions").Range
     
     Dim cl As Range
     
     For Each cl In objListRows.SpecialCells(xlCellTypeVisible)
       
       Dim RowNumber As Integer
       
       RowNumber = cl.Row
       
       If cl.Value = TargetValue And cl.Column = 4 Then
         ActiveSheet.Cells(RowNumber, 5) = Now()
       End If
     
     Next

  End If
  

End Sub

Open in new window

0
 
TocogroupAuthor Commented:
Thanks for that. The filter was a safeguard against trawling through hundreds (or thousands) of rows in the future.
Toco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now