Solved

How do populate filtered rows in an Excel VBA application ?

Posted on 2013-11-27
6
466 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
Comment Utility
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
Comment Utility
Excellent. Does exactly what I wanted. Many thanks for your help.
Toco
0
 

Author Comment

by:Tocogroup
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 15

Expert Comment

by:ChloesDad
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for that. The filter was a safeguard against trawling through hundreds (or thousands) of rows in the future.
Toco
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now