IF, TEXT and TODAY() VBA statement

[neilby]

Hi all,

within a macro I am trying to apply an IF statement to a cell.

Basically IF the current date = Monday then return the date from 3 days prior, else display date 1 day prior.

eg:
current date = 4th July 2016(ie a Monday) = result 1st July 2016 (ie previous Friday date)
current date = 5th July 2016(ie not a Monday) = result 4th July 2016 (ie previous days date)

I was trying to achieve in a 3 step process based on the basic formulae that works within the worksheet (admittedly almost certainly not the prettiest of code, especially when it doesn't work).

Worksheet formulae that does return the desired results
Cell A1 = TODAY()
Cell B1 = =TEXT,"ddd")
Cell C1 = =IF(L1="Mon",(TODAY()-3,TODAY()-1)

above formulae attempted as VBA below
However if this process is tried within VBA
Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"

this 2nd statement fails (returns #NAME)
Range("B1").Select
ActiveCell.FormulaR1C1 = "=TEXT(A1,""ddd"")"

and therefore statement 3 also fails
Range("C1").Select
ActiveCell.FormulaR1C1 = "=IF(B1=""Mon"",(TODAY()-3),(TODAY()-1))" returns the same #NAME


I should be grateful if someone could assist with a correct VBA syntax for the above

Regards

 

[BarryL]

could you try:

Sub test()
Dim dtdat As String
dtdat = Format(Date, "DDD")
Select Case dtdat
Case Is = "Mon"
Range("C1").Value = Date - 3
Case Else
Range("C1").Value = Date - 1
End Select
End Sub

 

[Osvaldo Palmeiro]

Try

Sub test()
 [A1] = "=TODAY()"
 [B1] = "=TEXT(A1,""ddd"")"
 [C1] = "=IF(B1=""Mon"",TODAY()-3,TODAY()-1)"
End Sub

 

[neilby]

Thanks BarryL, worked a dream. Not come across Case before, very useful.

could you try:

Sub test()
Dim dtdat As String
dtdat = Format(Date, "DDD")
Select Case dtdat
Case Is = "Mon"
Range("C1").Value = Date - 3
Case Else
Range("C1").Value = Date - 1
End Select
End Sub