VBA code to search a column of dates and return teh day in another column

[amxtomzo]

Hello and thanks for helping

Column C has dates formatted mm/dd/yyyy

I would like a VBA code to search column C, and any date that is a Sunday, the word Sunday would then appear in column B next to that date

This should only return Sunday, no other days need to appear in column B

Thanks

Thomas

 

[lrobbo314]

How about this?

Sub Sundays()
Dim r As Range: Set r = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
r.Offset(, -1).Value = Evaluate(Replace("If(WeekDay(@)=1,""Sunday"","""")", "@", r.Address))
End Sub

 

[JLGWhiz]

Another way

Sub t()
Dim c As Range
    With ActiveSheet
        For Each c In .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
        If Format(c, "w") = 1 Then
            c.Offset(, -1) = "Sunday"
        End If
    Next
End Sub

 

[Rick Rothstein]

How about this?

Sub Sundays()
Dim r As Range: Set r = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
r.Offset(, -1).Value = Evaluate(Replace("If(WeekDay(@)=1,""Sunday"","""")", "@", r.Address))
End Sub

Same underlying method, but this code is a bit more compact and avoids the creation of the variable...

[table="width: 500"]
[tr]
	[td]Sub Sundays()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Offset(, -1).Value = Evaluate("If(WeekDay(" & .Address & ")=1,""Sunday"","""")")
  End With
End Sub[/td]
[/tr]
[/table]

 

[amxtomzo]

Irobbo314 & Rick thank you so much for your help,
yes it worked,
except it cleared out exiting data and left blank cells,
My fault, completely, I should have been more specific

However i think JLGWhiz read my mind,
worked and no other data was changed
this is just what needed to move on to the next step,

as always, thank you so much for all you have taught me

Thomas

 

[Rick Rothstein]

Irobbo314 & Rick thank you so much for your help,
yes it worked,
except it cleared out exiting data and left blank cells,
My fault, completely, I should have been more specific

Ah, there is existing data in Column B... yes, you should have mentioned that. Here is my code modified to leave the existing data alone if it is not a Sunday date in the next cell over...

[table="width: 500"]
[tr]
	[td]Sub Sundays()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp)).Offset(, -1)
    .Value = Evaluate(Replace("If(WeekDay(" & .Offset(, 1).Address & ")=1,""Sunday"",IF(@="""","""",@))", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[amxtomzo]

Rick
Worked great, thank you so much for all your help
Past, Present and future

Thomas