if Then Day

[billandrew]

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

 

[billandrew]

Yes Sunday would have to be Unique as Thursday would be

 

[My Aswer Is This]

Why can you not tell us exactly what you want?

If value= "Monday" then Value= "M"
If Value= "Tuesday then value= "Cake"


Telling us you want unique values just has us guessing what you want.

 

[billandrew]

My apologies....

if value = Thursday then value = "H"
if value = Sunday then value = "U"

TY

 

[footoo]

My apologies....

if value = Thursday then value = "H"
if value = Sunday then value = "U"

TY

Sub f()
Dim r$: r = [B1:B1000].Address
[C1:C1000] = Evaluate(Replace("=If(#=""Thursday"",""H"",If(#=""Sunday"",""U"",Left(#,1)))", "#", r))
End Sub

 

[My Aswer Is This]

Try this:

Sub Check_Weekday()
'Modified 7-18-17 12:57 AM EDT
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To 1000
    With Cells(i, 3)
        MyVal = Cells(i, 2).Value
            Select Case MyVal
                Case "Sunday"
                    .Value = "U"
                Case "Monday"
                    .Value = Left(Cells(i, 2), 1)
                Case "Tuesday"
                    .Value = Left(Cells(i, 2), 1)
                Case "Wednesday"
                    .Value = Left(Cells(i, 2), 1)
                Case "Thursday"
                    .Value = "H"
                Case "Friday"
                    .Value = Left(Cells(i, 2), 1)
                Case "Saturday"
                    .Value = Left(Cells(i, 2), 1)
            End Select
        End With
    Next
Application.ScreenUpdating = True
End Sub

 

[billandrew]

Works great!!!

Thank you...

 

[Rick Rothstein]

Works great!!!

I think this much shorter code should also work for you...

[table="width: 500"]
[tr]
	[td]Sub FirstWeekdayLetter()
  [C1:C1000] = [INDEX(MID("UMTWHFS",(FIND(LEFT(B1:B1000&"  ",2),"SuMoTuWeThFrSa  ")+1)/2,1),)]
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

Mr. Rothstein

The code worked with the exception of the first row which contains a title "Day" returned #VALUE error

 

[Rick Rothstein]

Mr. Rothstein

The code worked with the exception of the first row which contains a title "Day" returned #VALUE error

Then change the C1 to C2 and the B1 to B2 in order to skip the first row which is apparently contains header cells.

Sub FirstWeekdayLetter()
  [C2:C1000] = [INDEX(MID("UMTWHFS",(FIND(LEFT(B2:B1000&"  ",2),"SuMoTuWeThFrSa  ")+1)/2,1),)]
End Sub

 

[billandrew]

Guess I should have noticed that...

Thanks again...