if Then Day

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: if Then Day

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Posts
    285
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default if Then Day

    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

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,191
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if Then Day

    Code:
    Sub v()
    [C1:C1000] = [INDEX(LEFT(TEXT(B1:B1000,"ddd"),1),)]
    End Sub
    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(B1:B1000,1),)]
    End Sub
    Last edited by footoo; Jul 16th, 2017 at 10:21 PM.

  3. #3
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,191
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if Then Day

    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

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,413
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: if Then Day

    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"

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    285
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if Then Day

    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.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,413
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: if Then Day

    Quote Originally Posted by billandrew View Post
    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.
    You want to change "Sunday" to what?
    Do not understand 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"

  7. #7
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,191
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if Then Day

    @billandrew
    Did you try the macro I posted? :

    Code:
    Sub v()
    [C1:C1000] = [INDEX(LEFT(B1:B1000,1),)]
    End Sub

  8. #8
    Board Regular Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,124
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    Default Re: if Then Day

    Hi
    if you want different values for Saturday & Sunday y not take the first 2 characters
    Code:
    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 & 2013 on Win 7

  9. #9
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,191
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if Then Day

    Quote Originally Posted by billandrew View Post
    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.
    Code:
    Sub v()
    Dim r$: r = [B1:B1000].Address
    [C1:C1000] = Evaluate(Replace("=if(#=""Sunday"",""$"",LEFT(#,1))", "#", r))
    End Sub
    Or if you want to show two letters instead of one :

    Code:
    Sub v()
    [C1:C1000] = [INDEX(LEFT(B1:B1000,2),)]
    End Sub
    Last edited by footoo; Jul 17th, 2017 at 09:06 AM.

  10. #10
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,191
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if Then Day

    ††
    Quote Originally Posted by billandrew View Post
    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.
    What about Tuesday and Thursday ?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com