=IF(WEEKDAY(TODAY())=2,"Monday","")

slifer666

Board Regular
Joined
Sep 29, 2005
Messages
81
ok i've got this line in a macro:
Code:
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(TODAY())=2,"Monday","")"

the purpose is to check if today is a monday. monday being the 2nd day of the week, according to the weekday() function

but it gives this VB error when editing the macro:
Compile Error
Expected: end of statement

and it has highlighted the 'Monday'

the thing that's bugging me, if i put this line into an edit box of a cell directly, it works:
Code:
=IF(WEEKDAY(TODAY())=2,"Monday","")

and i have other functions in the same macro which have the same format, and also work, eg:
Code:
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()-1"



any idea why i'm getting this error? i reckon its something really straightforward but i can't see it at the minute.

many thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
well are you sure the problem is there?
Dont u have another part of the code where you are using the "if then else" code and wherre you may have forget the closing tag?

can you post the full code of the macro?

edit :

i realise that you are using "," as separator of parameter in the formula... i dont know if it's due to the version but i have to use the ";"

Maybe it's the reason of the problem...
 
Upvote 0
does it give an error, or just not return anything? as today is day 6 :P
if you paste =IF(WEEKDAY(TODAY())=6,"FRIDAY","")
into a cell, does that return FRIDAY?
(i'm on xl2003, win2ksp4, with a few addin packs like morefunc, analysis toolpak and asap utils)

there's no IF ELSE's or any programming structures like that in the macro at all. its all straightforward stuff, editing cells and things that i basically recorded macros and then edited from there myself.

anyway, the full *unfinished* thing is currently:

Code:
Sub WorkpackageProcessingForTCTs()
'WorkpackageProcessingForTCTs Macro
'Macro recorded 17/11/2005 by slifer
'works for the first 5000 rows
'1 copy date into col N,
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],5)"
    Range("N3").Select
    Selection.AutoFill Destination:=Range("N3:N5000"), Type:=xlFillDefault
    Range("N3:N5000").Select
'2 strip leading apostrophe's from date in col N
    Dim rngCell As Range
    For Each rngCell In Selection.Cells
        rngCell.Value = rngCell.Value
    Next rngCell
'3 add day of week to column L (column Q, before the deletion in step 5), where 2 = monday
'***still need to code for mondays, on a monday it needs today-1, plus today-2, plus today-3
'=IF(WEEKDAY(TODAY())=2,"Monday!","")
'    Range("Q3").Select
'    ActiveCell.FormulaR1C1 = "=WEEKDAY(RC[-3])"
'    Range("Q3").Select
'    Selection.AutoFill Destination:=Range("Q3:Q5000"), Type:=xlFillDefault
'    Range("N1").Select
'    ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(TODAY())=2,"Monday","")"
'4 compare date of doc to today's date
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()-1"
    Range("P3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=RC[-2],TRUE,FALSE)"
    Range("O3:P3").Select
    Selection.AutoFill Destination:=Range("O3:P5000"), Type:=xlFillDefault
'5 delete columns A F G I J and rows 1 2.
    Range("A:A,F:F,G:G,I:I,J:J").Select
    Range("J1").Activate
    Selection.Delete Shift:=xlToLeft
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
'6 select true A:G with autofilter
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.AutoFilter
    Selection.AutoFilter Field:=11, Criteria1:="TRUE"
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:G5000").Select
End Sub
 
Upvote 0
well i maybe found something...

in the macro, the double quote symbol " is used to delimit the code... try do double it

try remplace the formula

ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(TODAY())=2,"Monday","")"

by this one

ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(TODAY())=2,""Monday"","""")"

i dont have anymore error
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top