[RESOLVED] Find Date

[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

[westconn1]

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

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

where s is a worksheet object

[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,

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

Lets say about step 2, (which gave error)

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

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

[westconn1]

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

I need to put some formula to extract Column number.

activecell.column

[sukumar]

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

[vbfbryce]

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

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

[westconn1]

Please help with Solution.

see post #2

[sukumar]

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

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

 
    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

[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.

[westconn1]

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

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