VBA: Sheet select, when any value in range is found

[pat767]

Hello all,

I'd need some help with this:

Given that I have 3 tabs (sheets), let's say: Alpha, Bravo and Charlie. I also have a 'data' sheet.

Let's assume that I have a =NOW() formula on Data!A1
I also have a list of 15 specific dates in 'Data', let's say from B1:B15

When running the sub, I want the code to "sniff" Data!A1 to detect today's date and if today's date corresponds to any of the given dates in B1:B15, the select a certain sheet (ex. Alpha) ... else, select sheet "Bravo".

Thanks for any help you can give!

 

[VoG]

Try

Sub atest()
Dim Found As Range
Set Found = Sheets("Data").Range("B1:B15").Find(what:=Date, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    Sheets("Bravo").Select
Else
    Sheets("Alpha").Select
End If
End Sub

 

[pat767]

Hello again,

I couldn't seem to intergrate this properly, especially after a few mods of my own.

Basically, I am using a macro in another workbook, which calls up the functions as described below.

The problem I am having is that it ends up selecting the "Semaine" sheet, even if the PC's date is changed to one of the dates in B2:B15.

Here is the code...

Sub Ouverture14()

' All throughout, the N1 value is today's date and O1 value is the day of the week, from 1 to 7

    Workbooks.Open Filename:= _
       "\\corpo.stm.info\services\Centre de controle\Exploitation\JOURNAUX_CCM\MODELES\Ouverture CCM 1-4.xlt" _
       , Editable:=True

Dim Found As Range
Set Found = Sheets("Listes").Range("B2:B15").Find(what:=Sheets("Semaine").Range("N1").Value, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then
Sheets("Dimanche et fêtes").Select
Application.Run "'Ouverture CCM 1-4.xlt'!dateouvertureLAVAL"
Else
Select Case Range("O1").Value
'(Selects the right sheet according to the day of the week.)
Case 7
Sheets("Samedi").Select
Application.Run "'Ouverture CCM 1-4.xlt'!dateouvertureLAVAL"

Case 1
Sheets("Dimanche et fêtes").Select
Application.Run "'Ouverture CCM 1-4.xlt'!dateouvertureLAVAL"

Case 2, 3, 4, 5, 6
Sheets("Semaine").Select
Application.Run "'Ouverture CCM 1-4.xlt'!dateouvertureLAVAL"
End Select

End If

strPath = "\\corpo.stm.info\services\Centre de controle\Exploitation\JOURNAUX_CCM\"
ActiveWorkbook.SaveAs Filename:=strPath & "Ouverture CCM 1-4 " & Worksheets("Semaine").Range("N1").Value, FileFormat:=xlWorkbook, AccessMode:=xlShared
ActiveWorkbook.Close True
End Sub

I apologize for the poor formatting; I uploaded this post using my iPhone.

 

[VoG]

The problem is that NOW() returns Date and Time whereas you are looking only for the Date. If you use Date as I did then you should not have that problem.

 

[pat767]

The problem is that NOW() returns Date and Time whereas you are looking only for the Date. If you use Date as I did then you should not have that problem.

Tried it with "=Date" with no success. It still selects the "Semaine" sheet.