If none of the cells in B1:B1000 is formatted as Date, then this shorter version could be used :Code:Sub v() [C1:C1000] = [INDEX(LEFT(TEXT(B1:B1000,"ddd"),1),)] End Sub
Code:Sub v() [C1:C1000] = [INDEX(LEFT(B1:B1000,1),)] End Sub
This is a discussion on if Then Day within the Excel Questions forums, part of the Question Forums category; I am wondering if there is an easier way to return the first letter of the weekday in VBA other ...
I am wondering if there is an easier way to return the first letter of the weekday in VBA other than what what I have here.
For X = 1 to 1000
If cells(x,2) = "Monday" then
cells(x,3) = "M")
Elseif cells(x,2 = "Tuesday" then
cells(x,3) = "T" and on to Sunday...
Thanks
If none of the cells in B1:B1000 is formatted as Date, then this shorter version could be used :Code:Sub v() [C1:C1000] = [INDEX(LEFT(TEXT(B1:B1000,"ddd"),1),)] End Sub
Code:Sub v() [C1:C1000] = [INDEX(LEFT(B1:B1000,1),)] End Sub
Last edited by footoo; Jul 16th, 2017 at 09:21 PM.
Correction to first code - to handle blank cells :
Code:Sub vv() [C1:C1000] = Evaluate("=if(LEN(B1:B1000),LEFT(TEXT(B1:B1000,""ddd""),1),"""")") End Sub
Try this:
Code:Sub Check_Weekday() Application.ScreenUpdating = False Dim i As Long For i = 1 To 1000 MyVal = Cells(i, 2).Value Select Case MyVal Case "Sunday" Cells(i, 3).Value = Left(Cells(i, 2), 1) Case "Monday" Cells(i, 3).Value = Left(Cells(i, 2), 1) Case "Tuesday" Cells(i, 3).Value = Left(Cells(i, 2), 1) Case "Wednesday" Cells(i, 3).Value = Left(Cells(i, 2), 1) Case "Thursday" Cells(i, 3).Value = Left(Cells(i, 2), 1) Case "Friday" Cells(i, 3).Value = Left(Cells(i, 2), 1) Case "Saturday" Cells(i, 3).Value = Left(Cells(i, 2), 1) End Select Next Application.ScreenUpdating = True End Sub
Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
Patience please I’m not perfect yet. "Memories are forever"
My answer is this
The case code worked well. Only thing I did not mention I would need to change Sunday to a unique value different from Saturday.
Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
Patience please I’m not perfect yet. "Memories are forever"
@billandrew
Did you try the macro I posted? :
Code:Sub v() [C1:C1000] = [INDEX(LEFT(B1:B1000,1),)] End Sub
Hi
if you want different values for Saturday & Sunday y not take the first 2 charactersCode:Sub Day() Dim i As Long For i = 1 To 1000 Range("C" & i) = Left(Range("B" & i), 2) Next i End Sub
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Running Office 2003 & Outlook 2010 on Win 7
Last edited by footoo; Jul 17th, 2017 at 08:06 AM.
Like this thread? Share it with others