Solved

Is this even possible?

Posted on 2007-03-27
4
213 Views
Last Modified: 2013-11-25
Is there a way to set the order by which a process searches a sheet for information? I.E, search from the bottom up as opposed to the top down?

I have code which searches sheets for a date and copies the row with that date to another sheet, it does it from the top down, so if there happen to be two rows with the same date on the same sheet, the first date it hits is the one it takes, then it moves to the next sheet, but what I need is for it to take the more recent entry, for instance if there were more than one date entered but each had different times, the second row with the same date would be the one with the more recent time.  So, if the search was conducted from the bottom  up, it would hit take the correct row needed.

This is my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address <> [G3].Address Then Exit Sub
          If Not IsDate(Target.Value) Then
               MsgBox "Value entered into " & Target.Address & " is not a date"
               Exit Sub
          End If
          If Target.Value > 0 Then
            Sheets("Hidden Data").Range("A1").Value = Target.Value
            Dim ws As Worksheet
            Dim fnd As Range
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            For Each ws In ThisWorkbook.Worksheets
                 If ws.Name <> "Status Report" And ws.Name <> "Hidden Data" Then
                      ws.Activate
                      Set fnd = ws.Range("A:A").Find(Target.Value, , xlValues, xlWhole)
                      If fnd Is Nothing Then
                        Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = "'"
                      End If
                      If Not fnd Is Nothing Then
                        fnd.EntireRow.Select
                           fnd.EntireRow.Copy
                           Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                      End If
                 End If
            Next
          End If
          Sheets("Status Report").Activate
          Application.ScreenUpdating = True
          Application.EnableEvents = True
     End Sub

I tried messing with a code like:

Sheets("PDS (1)").Select
    Range("A2").Select
    Range("A1:H4").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range( _
        "B2"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

To try to automatically change the sorting of the sheet before the process of searching the sheets was done, but couldn't find the correct way to get it implimented with the first bit of code.

Anyhow, first I just need to know if its possible at all, thus the lower points, will upgrade to 500 if someone can actually help me figure out how to get this to somehow work.  

The goal:  using first bit of code, grab not only the row with the correct date, but the most recent time as well.

Thank you all for your help.

Joe
0
Comment
Question by:josepharichard
  • 2
4 Comments
 
LVL 8

Expert Comment

by:stochastic
ID: 18806052
Joe,

Of course it is possible! There would be several ways to do this, including the sorting,
but in my opinion sorting is an overkill.

After you have found the target date, put a loop with the .findnext method to find
more records with the same date. Compare the time of the newly found records with
the latest time so far found, and if the new record is of a later time, just overwrite what
you pasted on the hidden sheet.

Let me know if you would like me to modify your code and post it here, but I thought
it would be more fun for you to get it working yourself!

- stochastic
0
 
LVL 13

Accepted Solution

by:
WJReid earned 500 total points
ID: 18806125
Hi Joe,

Can you put this formula in cell IV1 of each of the sheets, excluding the Hidden Data and Status Report Sheets:

=ROW(INDEX($A$1:$A$20000,SMALL(IF($A$1:$A$20000='Status Report'!$G$3,ROW($A$1:$A$20000)-ROW($A$1)+1,ROW($A$20000)+1),COUNTIF($A$1:$A$20000,'Status Report'!$G$3)),1))

This is an array formula, so it must be entered by clicking Ctrl+Shift+Enter together. You will know if it has entered correctly because there will be curly brackets around it. You can enter it in one sheet and just copy and paste to the others.

 Then try the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dRow As Double
    Dim dDate As Date
    If Target.Address <> [G3].Address Then Exit Sub
    If Not IsDate(Target.Value) Then
        MsgBox "Value entered into " & Target.Address & " is not a date"
        Exit Sub
    End If
    If Target.Value > 0 Then
        dDate = Range("G3").Value
        Sheets("Hidden Data").Range("A1").Value = Target.Value
        Dim ws As Worksheet
        Dim fnd As Range
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Status Report" And ws.Name <> "Hidden Data" Then
            ws.Activate
            If WorksheetFunction.CountIf(ws.Range("A1:A20000"), dDate) > 0 Then
                dRow = ws.Range("IV1").Value
                ws.Rows(dRow).EntireRow.Copy
                Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Else
                Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = "'"
            End If
        End If
        Next
    End If
    Sheets("Status Report").Activate
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Bill
0
 
LVL 13

Assisted Solution

by:WJReid
WJReid earned 500 total points
ID: 18806145
Hi Joe,
To save you having to put the formulas in, just put this line:
   ws.Range("IV1").FormulaArray = _
      "=ROW(INDEX(R1C1:R20000C1,SMALL(IF(R1C1:R20000C1='Status Report'!R3C7,ROW(R1C1:R20000C1)-ROW(R1C1)+1,ROW(R20000C1)+1),COUNTIF(R1C1:R20000C1,'Status Report'!R3C7)),1))"

afer this line;
  ws.Activate

and it will put the formulas in for you.
Bill
0
 

Author Comment

by:josepharichard
ID: 18806710
Bill, yer a genius, thank you so much, finally, this book is done, and I must say I've learned quite a bit in the process..
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

765 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