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

amxtomzo

Active Member
Joined
Nov 7, 2009
Messages
312
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
How about this?

Code:
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
 
Upvote 0
Another way
Code:
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
 
Upvote 0
How about this?

Code:
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...
Code:
[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]
 
Last edited:
Upvote 0
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
 
Upvote 0
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...
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top