IF DATE Statement help please

[Simon]

Hi I would like to use the following type of statement (I think
unfortunately my brain is in "Excel not VBA mode"):

IF

Then Else

Or Else

I wish to use the criteria that If date (date = 1st of the month) AND
Day = Sun, =Range("A1")
If Day = Sat AND DATE = 1 = Range("A2")
Else = Range("A3")

Thanks for your help

 

[Patrick Molloy]

I'm clear on what it is that you're tryign to achieve.


dim source as range
IF Day(Date)= 1 AND format$(date,"DDD") = "Sun" THEN
set source = Range("A1")
ELSEIF Day(Date)= 1 AND format$(date,"DDD") ="Sat" THEN
set source = Range("A2")
ELSE
set source = Range("A3")
END IF


some might suggest that instead of
format$(date,"DDD")
you could use the WEEKDAY() function. Makes no difference to the logic
either way

 

[Rick Rothstein]

Just as a follow up, this is what Patrick's code would look like using the
WeekDay function...

Dim Source As Range
If Day(Date) = 1 And Weekday(Date) = vbSunday Then
Set Source = Range("A1")
ElseIf Day(Date) = 1 And Weekday(Date) = vbSaturday Then
Set Source = Range("A2")
Else
Set Source = Range("A3")
End If

where I have used the built-in VB constants vbSunday (which is equal to 1)
and vbSaturday (which is equal to 7) for their self-documenting feature.

 

[Dave Peterson]

And another followup...

Sometimes using lots of if/then/else's can confuse me.

Dim Source As Range

set source = nothing 'a flag

select case day(date)
case is = 1
select case weekday(date)
case is = vbsunday
set source = range("A1")
case is = vbsaturday
set source = range("A2")
case else
set source = range("a3")
end select
case is = ....
'do more stuff???
end select

if source is nothing then
'not set
else
'do something with Source
end if

 

[Simon]

I'm clear on what it is that you're tryign to achieve.

dim source as range
IF Day(Date)= 1 AND format$(date,"DDD") = "Sun"  THEN
set source = Range("A1")
ELSEIF Day(Date)= 1 AND format$(date,"DDD") ="Sat" THEN
set source = Range("A2")
ELSE
set source = Range("A3")
END IF

some might suggest that instead of
format$(date,"DDD")
you could use the WEEKDAY() function. Makes no difference to the logic
either way








- Show quoted text -

As for Excel/VBA options are always abound, thanks for your suggestion
Patrick