How to Find a Date in a Row

[sourabh_ajmera]

Hello Everybody,

In another post I received help from @Rihabr & @Scott on how to find text in a specific range. (http://www.mrexcel.com/forum/excel-questions/798738-how-find-text-specific-range.html)

Currently, I am working on an excel sheet where I have to search for a specific 'Date' in row one and get the column letter of that column


My steps are:
In sheet 1
1. Find a specific day (from the above link)
2. Get the date corresponding to the 'day' in step 1
In sheet2
3. Use the date in step 2 to find it in another sheet in the same workbook. (always in row 1 - as headers)
4. Get the 'date' column letter (e.g: Column 1 is Column A, Column 2 is Column B and so on)


Here is my code:

Sub test()

'Find the specific day
Dim c As Range
Dim CellAdd As String
Set c = Range("C1:C7").Find("Friday")
CellAdd = c.Address(0, 0)


'Find the corresponding date to the day
Dim FriDate As String
FriDate = Range(CellAdd).Offset(0, -1).Value

'Find the Column letter corresponding to the date in sheet2
Sheets("sheet2").Select
Dim FindFriDate As Range
Dim FriDateCol As Long
Dim FriDatetxt As String

Set FindFriDate = Rows(1).Find(What:=FriDate, LookIn:=xlValues, LookAt:=xlWhole) <---- FriDate is not being read.
FriDateCol = FindFriDate.Column
FriDatetxt = Replace(Cells(1, FriDateCol).Address(False, False), "1", "")

End Sub


I have also tried something with direct date like;
Set FindFriDate = Rows(1).Find(What:="8/8/2014", LookIn:=xlValues, LookAt:=xlWhole) <---- This also being not read


Please help me with my code. I am beginer so I tend to miss small things.

Thank You in advance and feel free to ask any questions you have.

 

[JLGWhiz]

Maybe you were just starting the code with the wrong sheet being active. I took the liberty of modifying the code a little bit to add object variables for the sheets and then using With statements instead of Sheets.Activate. It seems to run OK. You might want to change the 'ActiveSheet' reference to the actual sheet name. I was not sure if it was Sheet1 or not.

Sub test()
'Find the specific day
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet
Dim CellAdd As String, FriDate As String
Dim FindFriDate As Range
Dim FriDateCol As Long
Dim FriDatetxt As String
Set sh1 = ActiveSheet 'Use actual sheet name here instead of ActiveSheet
Set sh2 = Sheets("Sheet2")
With sh1
    Set c = .Range("C1:C7").Find("Friday")
    CellAdd = c.Address(0, 0)
    'Find the corresponding date to the day
    FriDate = .Range(CellAdd).Offset(0, -1).Value
End With
'Find the Column letter corresponding to the date in sheet2
With sh2
    Set FindFriDate = .Rows(1).Find(What:=FriDate, LookIn:=xlValues, LookAt:=xlWhole) '<---- FriDate is not being read.
    FriDateCol = FindFriDate.Column
    FriDatetxt = Replace(Cells(1, FriDateCol).Address(False, False), "1", "")
End With
End Sub

 

[Peter_SSs]

Here is my code:

Where is your code? Is it in a standard module or one of the worksheet modules?
If a worksheet module, what is the name of that worksheet?

 

[sourabh_ajmera]

@JLGWhiz...will try it out and let you know. Thanks alot

@Peter....I am sorry I am new to the board I dont know how to put the code in the way @JLGWhiz did....I just wrote it down as a normal text.
Could you please kind enough and let me know how to do it so that it would be easier for people in the future to read and understand it. Thanks

 

[sourabh_ajmera]

@JLGWhiz: I tried your code but I am still getting the "Run-time error '91' : Object variable or With block variable not set"

Any suggestions? Please help. Thank You

 

[Peter_SSs]

@JLGWhiz: I tried your code but I am still getting the "Run-time error '91' : Object variable or With block variable not set"

Any suggestions? Please help. Thank You

Suggestion: When you get an error, as well as stating the error, state what line of code is causing the error (usually highlighted yellow when you click 'Debug' after the error occurs)



Perhaps you didn't understand JLGWhiz' instruction about this line

Set sh1 = ActiveSheet 'Use actual sheet name here instead of ActiveSheet

Try changing that line to

Set sh1 = Sheets("Replace this blue text with the name of the sheet you are looking in C1:C7 to find the day")

@Peter....I am sorry I am new to the board I dont know how to put the code in the way @JLGWhiz did....I just wrote it down as a normal text.
Could you please kind enough and let me know how to do it so that it would be easier for people in the future to read and understand it. Thanks

See the last line in my signature block below.