This month I will show you how to sum by a date range and other criteria. The method I use is the SUMPRODUCT Function, Data Validation and Conditional Formatting. If your table of data is large and/or you will be using multiple SUMPRODUCT Functions, you are better of with a PivotTable or Database Functions.
This month I want to show a simple method to loop through a Folder of a users choosing. For those who have upgraded to Excel 2007 you may, or may not, know the Application.FileSearch is not supported in 2007. This method WILL work for 97-2007 and beyond :)
Sub OutputFilesFolders() Dim fdFolder As FileDialog Dim strFolder As String Dim strExtension As String Dim lRow As Long Set fdFolder = Application.FileDialog(msoFileDialogFolderPicker) With fdFolder .Title = "Select a Folder" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub 'It's here that we pass the folder path to our Variable strFolder = .SelectedItems(1) End With ChDir strFolder 'Change extension or use: Dir("*") for all file types strExtension = Dir("*.xls") 'Put in our headings With Range("A1:B1") .Value = Array("FILE NAME", "FOLDER") .Font.Bold = True End With 'Loop through Folder and out file names and folder Do While strExtension <> "" With Cells(Rows.Count, 1).End(xlUp) .Offset(1, 0) = strExtension .Offset(1, 1) = strFolder End With strExtension = Dir Loop End Sub
See ya next month :)
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft