Solved

How do populate filtered rows in an Excel VBA application ?

Posted on 2013-11-27
6
475 Views
Last Modified: 2013-11-28
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
Comment
Question by:Tocogroup
  • 3
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 39681999
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
 

Author Closing Comment

by:Tocogroup
ID: 39682770
Excellent. Does exactly what I wanted. Many thanks for your help.
Toco
0
 

Author Comment

by:Tocogroup
ID: 39682810
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:ChloesDad
ID: 39683167
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
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39683248
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
 

Author Comment

by:Tocogroup
ID: 39683733
Thanks for that. The filter was a safeguard against trawling through hundreds (or thousands) of rows in the future.
Toco
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question