Board Regular
Sep 29, 2005
ok i've got this line in a macro:
    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:

and i have other functions in the same macro which have the same format, and also work, eg:
    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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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:

Sub WorkpackageProcessingForTCTs()
'WorkpackageProcessingForTCTs Macro
'Macro recorded 17/11/2005 by slifer
'works for the first 5000 rows
'1 copy date into col N,
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],5)"
    Selection.AutoFill Destination:=Range("N3:N5000"), Type:=xlFillDefault
'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
'    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
    ActiveCell.FormulaR1C1 = "=TODAY()-1"
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=RC[-2],TRUE,FALSE)"
    Selection.AutoFill Destination:=Range("O3:P5000"), Type:=xlFillDefault
'5 delete columns A F G I J and rows 1 2.
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlUp
'6 select true A:G with autofilter
    Selection.Insert Shift:=xlDown
    Selection.AutoFilter Field:=11, Criteria1:="TRUE"
    Range(Selection, Selection.End(xlDown)).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

Latest member

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