excel formula to VBA

[exceluser11]

Hi there,

Hoping to get some help with something I've been working on.

Short version is information is pasted into a specific cell (where my offset is current pulling from) and I need it to return today's date, or yesterday's date if it has the "Mon" "Tue" "Wed" etc.

I managed to figure it out using the below function, but I need the TODAY() function to be static, see below:

=IF(OFFSET('Raw Input'!$A1,(ROW()-1)*1,2)="","",(IF(SUM(COUNTIF(OFFSET('Raw Input'!$A1,(ROW()-1)*1,2),{"*Mon*","*Tue*","*Wed*","*Thu*","*Fri*","*Sat*","*Sun*"})),TODAY()-1,TODAY())))

The macro reader pulled it as

"=IF(OFFSET(RC1,(ROW()-1)*1,2)="""","""",(IF(SUM(COUNTIF(OFFSET(RC1,(ROW()-1)*1,2),{""*Mon*"",""*Tue*"",""*Wed*"",""*Thu*"",""*Fri*"",""*Sat*"",""*Sun*""})),TODAY()-1,TODAY())))"

I'm pretty new at using VBA so any suggestion s on how to translate it would be great!


Thanks!


EDIT:
alternatively if anyone could suggest a simple way to read a specific cell to determine if it says "Mon" "Tue" "Wed" etc to show yesterday's date (static, so it doesnt change ever) and if not, then today's date, again static so it doesnt change when i open the book tomorrow

 

[DanteAmor]

I do not know in which cell you have your formula, but with the following it calculates your formula and puts the static result
Change "D1" by your cell

Sub test()
    With Range("[COLOR=#ff0000]D1[/COLOR]")
        .FormulaR1C1 = "=IF(OFFSET(RC1,(ROW()-1)*1,2)="""","""",(IF(SUM(COUNTIF(OFFSET(RC1,(ROW()-1)*1,2),{""*Mon*"",""*Tue*"",""*Wed*"",""*Thu*"",""*Fri*"",""*Sat*"",""*Sun*""})),TODAY()-1,TODAY())))"
        .Value = .Value
    End With
End Sub