How to Find Text in a specific range

[sourabh_ajmera]

Hello Everybody,

I went through few posts online about my query but couldnt find anyhting specific. Please help me out

I am trying to find a particular text in finite range using macro.

I am first finding all the dates in the previous week and putting the values in column B (7 Values - B1:B7) and Converting those dates to respective days of the week in column C (C1:C7)
eg: '08/13/2014' in col B would be converted to 'Wednesday' in col C

So, now I want to find a specific day, say Friday, from column C. How do I do this??

Thank you in advance.

 

[Momentman]

Do you have in column C the day of the week like 1,2,3,4,5 or Sun, Mon,Tue,Wed,Thur,Fri and how do you mean finding a friday?

 

[sourabh_ajmera]

Hi Momentman,

I have like 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday','Saturday' & 'Sunday', all days of the week in Column C corresponding to the date.

Now out of these from Range(C1:C7), I want to find only one specific day. eg: 'Friday'

I hope I have explained myself clearly. Please let me know if u need anymore info.

 

[rihabr]

Once you find the day in column C, what do you want happening.. a message box telling you what cell it is, the cell being selected, highlighted..? Here is a code which loops through to find Friday and displays its cell address in a message box:

Sub FindDate()
Dim cell As Range
For Each cell In ActiveSheet.Range("C1:C7")
    If cell = "Friday" Then
        MsgBox ("Friday is in cell " & cell.Address)
        Exit Sub
    End If
Next cell
End Sub

 

[Scott Huish]

I don't quite understand. Friday would always be in C5 per your post. Why do you need to "find" it?

But if you do need to, I wouldn't use a loop:

Sub test()
Dim c As Range
Set c = Range("C1:C7").Find("Friday")
If Not c Is Nothing Then MsgBox "Friday is in cell " & c.Address(0, 0)
End Sub

 

[sourabh_ajmera]

Excellent! Thank you @Rihabr & @Scott.

Sorry about that...I did want to find the cell address itself.

@Scott: Friday will not always be in C5 as dates keep on changing so the range can start from any of the 7 days.

You guys made my day.....Thank you again