[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How do populate filtered rows in an Excel VBA application ?

Posted on 2013-11-27
6
Medium Priority
?
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
ChloesDad earned 2000 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
Technology Partners: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

650 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