Solved

How do populate filtered rows in an Excel VBA application ?

Posted on 2013-11-27
6
470 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 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