Changing Date Value to Week Day Name Vba

Question

I am trying to make the below formulae into VBA syntax. My objective is that when a user inputs a Date Value Starting from Cell (“A4”) the adjacent cell starting from cell (“F4”) will return the Day Name. However if there is no Date value in cell (“A4”) to last row the corresponding adjacent cell in column F should not have a value.

The Syntax I have an excel formulae is

=TEXT(WEEKDAY(A4),"dddd")

As a VBA recorder all I am getting is the below:

Sub Day_Names()
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(WEEKDAY(RC[-5]),""dddd"")"
    Range("F4").Select
    Selection.AutoFill Destination:=Range("F4:F29"), Type:=xlFillDefault
    Range("F4:F29").Select
End Sub

so the problem at the moment is actually that if a4 or a5 a6 and so on has no value then the corresponding F cell should be blank right? — KKowalczyk

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-05-22T11:45:15

However if there is no Date value in cell (“A4”) to last row the corresponding adjacent cell in column F should not have a value.

The formula that you need in F4 is =IF(A4="","",TEXT(WEEKDAY(A4),"dddd"))

You don't need to autofill. you can enter the formula in one go to the entire range. See this code

Sub Sample()
    Dim ws As Worksheet
    Dim Rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set Rng = ws.Range("F4:F29")

    Rng.Formula = "=IF(A4="""","""",TEXT(WEEKDAY(A4),""dddd""))"
End Sub

enter image description here

Changing Date Value to Week Day Name Vba

1 Answers