Posts by dwd

Hi Carim,

Sorry for late reply. Been away. At the moment it seems to work. I'm not convinced I've sorted it. I'll box on for now but may need to revisit later. Thanks for your help so far.

Hi Carim,

Thanks for your reply. I'm not sure what you mean or how I use that here.

The Table is a database of investigation files. When completed the cell in Column N is formatted Red indicating the information that now needs to be copied across into the other spreadsheet. Once that is completed, the Red background automatically disappears (by design).

The rows won't be contiguous hence the need to go from the first Red cell through to the last used cell detecting all Red cells.

Hi,

Hoping for some enlightenment please.

I converted a spreadsheet of data to a Table (TableMain).

Depending on certain conditions, I have Conditional Formatting to format the relevant cells in Column N with a Red background (Interior Color).

What I need to do is then copy the contents of the rows belonging to those particular cells in Column N to another spreadsheet.

However, what worked before I converted to the Table now doesn't recognise the interior color now in order to detect them and copy etc.

I need to detect the first Red cell in the column, copy the row's contents to the other spreadsheet and return to pick up the next Red cell's row to copy etc until finished.

Any help appreciated.

Re: Select the first coloured cell in a range.

Fantastic. Just what the doctor ordered. Thank you.

Re: Select the first coloured cell in a range.

Hi,
How would I then have another macro which finds the next yellow cell?
I tried duplicating the macro and inserting a For..Next statement but doesn't seem to recognise 'For'.

Thanks again.

Re: Select the first coloured cell in a range.

Hi.
Great, does the trick.
Thanks.

Re: Select the first coloured cell in a range.

Yes, sorry. Forgot about the conditional format.
However the reply from sktneer has put me on the right rack.
Thanks very much.

Hi,

Depending on certain conditions, cells in Column B are coloured yellow.

I'm looking for a macro that that will take me to the first yellow coloured cell in the Column.

Thanks in advance.

dwd

Re: VBA to sum certain rows based on month

That's great. Thanks very much.

Hi,

I have a dynamic range that rows are added to each day.

Column A contains a file number.
Column F contains the month was file was dealt with e.g. October.
Columns J:P contain a '1' based on a condition relating to the file for each of those columns. (Only one column will contain the '1')

What I'm after is to search the range used and for the rows where October is entered, sum all the '1's and place the total in cell U3.

Thanks

Re: Formula for OR with MATCH functions

Thanks very much. I was on the right track but you showed me the bit I was missing. Cheers.

I have two workbooks - Book1 and Book2.
Book1 contains a list of names and corresponding ID numbers for a summary report.

Book2 contains more names and ID numbers with extra data. Unfortunately, using Excel 2007, the list in Book2 exceeds the row limitations in sheet 'IDs' and overflows into sheet 'IDs(1)'.
The formula below worked well to identify matches with data in the 'IDs' sheet but I now need to also look in the 'IDs(1)' sheet as well.
Data starts on row 5 in the 'IDs' sheet (hence the '>4' in the formula) and row 1 in the 'IDs(1)' sheet.

=IFERROR(IF(MATCH(D29,[Book2.xls]IDs!$B:$B,0)>4,"Duplicate",""),"")

I need some sort of OR statement that will look in both sheets for a match as it is possible the same ID could be in either or both sheets.

Thanks in anticipation.

Re: Function not working now upgraded to 2007

Hi,

Thanks for that. Unfortunately that hasn't worked either.

The included code below pulls back all the .pdf files in a given directory (and sub-folders) and lists them in a worksheet.
Within the spreadsheet an IF formula then reads each row in the the date column and compares them to any date I specify telling me if the .pdf date is newer than my specified date.

This code worked great in Excel 2002 but my computer received and upgrade to Excel 2007 and the dates returned are all out of whack.
Some are returned formatted as 'General', some as 'Custom dd/mm/yyyy hh:mm:ss' (my preferred format), and some returned with the day and month reversed.
Those that are not returned as my preferred format all falsely show they are newer than my specified date when using the IF formula.

I've tried a few things but still can't get the code to bring back the list in the correct format.

Any help would be appreciated. Thanks.

Private Function SearchFiles(myDir As String _    , myFileName As String, n As Long, myList()) As Variant
    Dim fso As Object, myFolder As Object, myFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each myFile In fso.getfolder(myDir).Files
        If (Not myFile.Name Like "~$*") * (myFile.Name <> ThisWorkbook.Name) _
        * (myFile.Name Like myFileName) Then
            n = n + 1
            ReDim Preserve myList(1 To 4, 1 To n)
            myList(1, n) = myDir
            myList(2, n) = myFile.Name
            myList(3, n) = myFile.Size
            myList(4, n) = myFile.DateLastModified
        End If
    Next
    For Each myFolder In fso.getfolder(myDir).subfolders
        SearchFiles = SearchFiles(myFolder.Path, myFileName, n, myList)
    Next
    SearchFiles = IIf(n > 0, myList, "")
End Function

Re: List all filenames in directory on a worksheet (including file size)

Not sure sure whether this should be a reply or new post.

The code below pulls back all the .pdf files in a given directory (and sub-folders) and places them in a worksheet.
An IF formula then reads each row in the the date column and compares them to any date I specify telling me if the .pdf date is newer than my specified date.

This code worked great in Excel 2002 but my computer received and upgrade to Excel 2007 and the dates returned are all out of whack.
Some are returned as 'General', some as 'Custom dd/mm/yyyy hh:mm:ss' (my preferred format), and some returned with the day and month reversed.
Those that are not returned as my preferred format all falsely show they are newer than my specified date using the IF formula.

I've tried a few things but still can't get the code to bring back the list in the correct format.

Any help would be appreciated. Thanks.

Private Function SearchFiles(myDir As String _
    , myFileName As String, n As Long, myList()) As Variant
    Dim fso As Object, myFolder As Object, myFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each myFile In fso.getfolder(myDir).Files
        If (Not myFile.Name Like "~$*") * (myFile.Name <> ThisWorkbook.Name) _
        * (myFile.Name Like myFileName) Then
            n = n + 1
            ReDim Preserve myList(1 To 4, 1 To n)
            myList(1, n) = myDir
            myList(2, n) = myFile.Name
            myList(3, n) = myFile.Size
            myList(4, n) = myFile.DateLastModified
        End If
    Next
    For Each myFolder In fso.getfolder(myDir).subfolders
        SearchFiles = SearchFiles(myFolder.Path, myFileName, n, myList)
    Next
    SearchFiles = IIf(n > 0, myList, "")
End Function

Re: List all filenames in directory on a worksheet (including file size)

Thanks for the link. Was after the property name. Thanks again.

Re: List all filenames in directory on a worksheet (including file size)

Just one further question.
To add file Modified date please.

Re: List all filenames in directory on a worksheet (including file size)

Thanks for help cytop.
I managed to change myFileName to myFile.Name in the extra line of code.
Works magic.
Thanks again.

Re: List all filenames in directory on a worksheet (including file size)

Thanks cytop, but it keeps coming up 'No file found'.
Am I missing something else?

I have located help from this forum on listing all files from a directory into a spreadsheet (see code below).

I need to be able to pull the file size for each file, but can't see how to do it.

I would appreciate it someone could rewrite the code to include file size.
Thanks in advance.

Sub test() 
    Dim myDir As String, myList() 
    With Application.FileDialog(msoFileDialogFolderPicker) 
        If .Show = True Then 
            myDir = .SelectedItems(1) 
        End If 
    End With 
    On Error Resume Next 
    myList = SearchFiles(myDir, "*.xls*", 0, myList()) 
    If Err = 0 Then 
        Sheets(1).Cells(1).Resize(UBound(myList, 2), UBound(myList, 1)).Value = _ 
        Application.Transpose(myList) 
    Else 
        MsgBox "No file found" 
    End If 
    On Error Goto 0 
End Sub 
 
 
Private Function SearchFiles(myDir As String _ 
    , myFileName As String, n As Long, myList()) As Variant 
    Dim fso As Object, myFolder As Object, myFile As Object 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    For Each myFile In fso.getfolder(myDir).Files 
        If (Not myFile.Name Like "~$*") * (myFile.Name <> ThisWorkbook.Name) _ 
        * (myFile.Name Like myFileName) Then 
            n = n + 1 
            Redim Preserve myList(1 To 2, 1 To n) 
            myList(1, n) = myDir 
            myList(2, n) = myFile.Name 
        End If 
    Next 
    For Each myFolder In fso.getfolder(myDir).subfolders 
        SearchFiles = SearchFiles(myFolder.Path, myFileName, n, myList) 
    Next 
    SearchFiles = IIf(n > 0, myList, "") 
End Function