=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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

sebiwane

New Member
Joined
Nov 16, 2005
Messages
11
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...
 

slifer666

Board Regular
Joined
Sep 29, 2005
Messages
81
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
 

sebiwane

New Member
Joined
Nov 16, 2005
Messages
11
i just copy the code of the macro and go same error :s
i'll check if i see something
 

sebiwane

New Member
Joined
Nov 16, 2005
Messages
11

ADVERTISEMENT

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
 

slifer666

Board Regular
Joined
Sep 29, 2005
Messages
81
ahh thats it, many thanks. i needed more """""'s it seems. cheers :D
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,637
Members
412,334
Latest member
ExcelForLifeDontHate
Top