[RESOLVED] Find Date

Hi,

In Sheet1, I want to get following: -

1. find yesterday's date
2. address of first cell containing yesterday's date
3. row number of first cell containing yesterday's date
4. column number of first cell containing yesterday's date
5. using column number from step 4, I want to remove all values from cells in that column, which do not contain any time.

I have clues about Step 1, 3 and 4.

But, I am not able to link Step 1 to 5 together.

With thanks,
Sukumar

Quote:

---

I want to remove all values from cells in that column, which do not contain any time

---

pls explain more

use excels find method for 2.
maybe like

Code:

---

Set fnd = s.UsedRange.Find(Date - 1)
rw = fnd.row
col = fnd.column

---

where s is a worksheet object

Quote:

---

Originally Posted by sukumar

Hi,

In Sheet1, I want to get following: -

1. find yesterday's date
2. address of first cell containing yesterday's date
3. row number of first cell containing yesterday's date
4. column number of first cell containing yesterday's date
5. using column number from step 4, I want to remove all values from cells in that column, which do not contain any time.

I have clues about Step 1, 3 and 4.

But, I am not able to link Step 1 to 5 together.

With thanks,
Sukumar

---

Hi,

Lets say about Step 1,

Code:

---

td = Format(Date -1, "dd-mm-yyyy")

---

Lets say about step 2, (which gave error)

Code:

---

Cells.Find(What:="09-Dec-16", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

---

Lets say about Step 3

Code:

---

Activecell.address

---

I need to put some formula to extract Column number.

Lets say about Step 5.

Please refer to attachment here Attachment 143145, where Range("G15") and Range("G20") contain text values, which I want to remove, and replace by Time Values.

With thanks,
Sukumar

Quote:

---

Cells.Find(What:="09-Dec-16",

---

this searches for a text string and may not match a date value, even though they look the same

Quote:

---

I need to put some formula to extract Column number.

---

activecell.column

Quote:

---

Originally Posted by westconn1

this searches for a text string and may not match a date value, even though they look the same

---

Hi,

I thank you for feedback. Please help with Solution.

With thanks,
Sukumar

I'm unclear what you mean by step 5, but:

Code:

---

Sub findDate()
    Dim ws As Worksheet
    Dim fnd As Range
   
    Set ws = ActiveSheet
    Set fnd = ws.UsedRange.Find(Date - 1)
    MsgBox fnd.Address & vbCrLf & fnd.Row & vbCrLf & fnd.Column
    'the above shows the address, the row and the column
    With ws.Columns(fnd.Column)
        'what does "not contain any time" mean?
    End With
End Sub

---

Quote:

---

Please help with Solution.

---

see post #2

Excellent job until here, but experts put unreliable remarks for usedrange questionable

Quote:

---

Originally Posted by vbfbryce

I'm unclear what you mean by step 5, but:

Code:

---


    With ws.Columns(fnd.Column)
        'what does "not contain any time" mean?
    End With
End Sub

---

---

Let me explain steps for 'what does "not contain any time" mean?

Step1: Code found first row as fnd.row

Step 2: Code to filter fnd.row and filter out only Text Values, but not time values in fnd.column

Step 3: Code to Delete all rows which have Text Value, but not time values in fnd.column

With thanks,
Sukumar

Quote:

---

Originally Posted by sukumar

Please refer to attachment here Attachment 143145, where Range("G15") and Range("G20") contain text values, which I want to remove, and replace by Time Values.

---

Following URL address explains why Run Time Error 91 happens.

Quote:

---

experts put unreliable remarks for usedrange

---

but in this case it will not affect the results

you can test each cel in fnd.column, to see if it is a time only value like

Code:

---

ndrow = Cells(Rows.Count, fnd.Column).End(xlUp).Row
For rw = ndrow To 2 Step -1
    If IsDate(Cells(rw, fnd.Column).Text) And Cells(rw, fnd.Column).Value2 < 1 Then
        ' time value, do nothing
    Else
        Cells(rw, 1).EntireRow.Delete
    End If
Next

---

not properly tested, but runs without error
note this will also delete the row with yesterdays date as it is in the column being checked
i specified to check to row 2 if no headers change to row 1
if this is incorrect then the information was not clear