PDA

View Full Version : Weekday function



Valentino142
03-09-2011, 03:14 AM
Hi All,

In one of my macro's, which performs submacro's for weekdays only, I would like to add a certain macro which only performs if the weekday is a friday. However, as i've set it up now, it completely skips the submacro even on fridays. Can somebody help me on this, as I'm not sure what I'm overlooking? Thx

The original code of the main macro is:


Sub RunPeriod()

Dim i As Date

i = Range("StartTest")

Do While i < Range("LastMarket") + 1

If Weekday(i, vbSaturday) > 2 Then

Range("b2").Value = i
[Macro]
Debug.Print i
End If

i = i + 1


Loop

End Sub


The new submacro, which is part of "Macro" in the code above, is of the form:

Sub NewWeek()
'
' NewWeek Macro
'

'
If Weekday(i, vbSaturday) > 6 Then
[Submacro code]
End If

As indicated, it moves immediately to "End if", even on Fridays. What am I doing wrong??

Hopefully I've described the problem clearly, if not please indicate as I will be happy to give additional info to get this gremlin solved!

Thanks in advance

Valentino

Bob Phillips
03-09-2011, 04:12 AM
That will be because it knows nothing about the value in i, you need to pass the date to your new macro.

Valentino142
03-09-2011, 04:21 AM
Xld,

thanks for your reply. I suspected that was the problem, however since submacro is part of macro i suspected and hoped it would somehow remember i.

Perhaps it would be easier to move submacro to where date is defined, such as:


Sub RunPeriod()

Dim i As Date

i = Range("StartTest")

Do While i < Range("LastMarket") + 1

If Weekday(i, vbSaturday) > 2 Then

Range("b2").Value = i
[Macro]
Debug.Print i
End If

If Weekday(i, vbSaturday) > 6 Then
[Submacro code]
End If


i = i + 1


Loop

End Sub

Do you think this could work?

Thanks again
Val

Bob Phillips
03-09-2011, 06:18 AM
It could, it should, and that is what I would so.

BTW, the new macro cannot 'remember' something because, by declaring i in the upper procedure, you have restricted its scope to that procedure.

Bob Phillips
03-09-2011, 06:18 AM
BTW, why not test for = 7 rather than > 6?