=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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,545
Messages
5,838,004
Members
430,525
Latest member
WHall

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
Top