-2

I'm looking to write a code to convert days into a specific series. eg.

If it is Monday I want - N/Y/N/N/N/N/N
If it is Wednesday I want - N/N/N/Y/N/N/N
if it is Sunday, Friday I want - Y/N/N/N/N/Y/N

is there any way I can do this in VBA or excel.

Thanks in advance.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
Deepak Ahuja
  • 1
  • 3

3 Answers3

1

This uses arrays to create the pattern you want.

Sub trnsfrm()
Dim rang As Range
Set rang = Worksheets("Sheet4").Range("A1:A3") ' Change to your range and worksheet

Dim rng() As Variant
rng = rang.Value

Dim WeekDy As Variant
WeekDy = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Dim outArr(1 To 7) As Variant

Dim j As Long
For j = LBound(rng) To UBound(rng)
    Dim i As Long
    For i = LBound(WeekDy) To UBound(WeekDy)
        If InStr(rng(j, 1), WeekDy(i)) > 0 Then
            outArr(i + 1) = "Y"
        Else
            outArr(i + 1) = "N"
        End If
    Next i
    rng(j, 1) = Join(outArr, "/")
Next j

rang.Value = rng


End Sub

Turns:

enter image description here

Into:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • @ScottCraner I believe both of us made a mistake spending our time on a question that was not well written and the asker just used us to get done whatever is his task, he writes perfectly worked for all of the answer (and they work) while he does not even bother to upvote the answers to appreciates people's time. I am not going to make the same mistake again and I hope you would not do that too – Ibo Aug 11 '18 at 20:31
  • @Ibo good luck with that. Unless you stop answering, which I suggest you continue as many of your answers are better than the accepted, this one included, there will be those that you feel you wasted your time. I have had answers that are better than the accepted answer ignored and I have had answers of mine that are not as good as others accepted. It goes both ways. Remember that many time we do not answer for the immediate problem but those who may have similar questions that come later. – Scott Craner Aug 11 '18 at 20:36
  • @ScottCraner I hear you, and I agree with what you say, but I mean answering questions that did not put enough efforts even to write a good question is a mistake by experienced members in here, I will not do that again since I feel I have been used – Ibo Aug 12 '18 at 02:35
  • @Ibo I understand, most time I do not answer, I only answered this one because of the puzzle. I did it to hone my skills. I wanted to practice with arrays. – Scott Craner Aug 12 '18 at 03:19
  • @Ibo I feel your pain. In this case I think the op only did need the excel non-array formula I posted (the wording is questionable). Regardless, it can be a thankless job answering poor questions, putting in time and only to get nothing out of it. Remember that you’re also sharpening your own skills in the process . I consider answering questions kind of like doing a cross word puzzle. I answer questions truly for my own entertainment. If it makes you feel any better, the OP‘s score prevents him giving you an upvote. I gave you a tick though as your answer is cool. ✔️‍♂️⚡️ – pgSystemTester Aug 12 '18 at 05:51
  • @ScottCraner I gave you an upvote, totally forgot that I could do it LOL – Ibo Aug 12 '18 at 07:10
  • @PGCodeRider thanks! I know everyone has his own reasoning, but this way I learn how to better choose the questions – Ibo Aug 12 '18 at 07:11
1

I was thinking very similar to Scott's approach, but I found a shorter way to do this. Place the code in a module and you can have access to it in the worksheet as a function:

Function TagDays(rng As Range)
    strDays = "SUNDAY1MONDAY2TUESDAY3WEDNESDAY4THURSDAY5FRIDAY6SATURDAY7)"
    arr = Array("N", "N", "N", "N", "N", "N", "N")
    For Each cell In rng
        If cell <> "" Then
            i = InStr(strDays, UCase(cell))
            If i > 0 Then arr(Mid(strDays, i + Len(cell), 1) - 1) = "Y" '-1 since the LBOUND of arr is 0 not 1
        End If
    Next cell
    TagDays = Join(arr, "/")
End Function

Call the function and choose the range you want, it can be more than one column and the result should be updated on the spot even you add or remove days. It is not case sensitive too. Also, it ignores the blank cells.

enter image description here

Output:

enter image description here

EDIT: the below code can handle the days separated by comma in one cell too, spaces don't matter

Function TagDays(rng As Range)
    strDays = "SUNDAY1MONDAY2TUESDAY3WEDNESDAY4THURSDAY5FRIDAY6SATURDAY7)"
    arr = Array("N", "N", "N", "N", "N", "N", "N")
    For Each cell In rng
        If cell <> "" Then
            arr2 = Split(cell.Value, ",")
            For j = LBound(arr2) To UBound(arr2)
                strday = Trim(UCase(arr2(j)))
                i = InStr(strDays, strday)
                If i > 0 Then arr(Mid(strDays, i + Len(strday), 1) - 1) = "Y" '-1 since the LBOUND of arr is 0 not
            Next j
        End If
    Next cell
    TagDays = Join(arr, "/")
End Function

enter image description here

OUTPUT:

enter image description here

Applying the function to cells individually (like Scott's example) will give you this:

enter image description here

Ibo
  • 4,081
  • 6
  • 45
  • 65
  • According to the OP he wanted it in place not in another cell, and that each cell may contain more than one day delineated by a comma. See the comments. – Scott Craner Aug 09 '18 at 23:02
  • @ScottCraner I feel that you suggested to replace the values in place, which in fact is not a good idea because there is no UNDO chance. It seems the asker did not have a clear idea of what he wanted. He answered yes because he did not want to lose the chance of getting help. Honestly, we should have made him edit his question before we offered him free codes – Ibo Aug 09 '18 at 23:33
  • I added an edit that can handle the comma separated cases too – Ibo Aug 09 '18 at 23:51
0

You don't really need VBA to do this. Just use this formula (assuming it's column A). Reminder alt enter gives you an enter in a formula to improve readibility.

=IF(ISERROR(MATCH("Sunday",A:A,0)),"N","Y")&"/"&
 IF(ISERROR(MATCH("Monday",A:A,0)),"N","Y")&"/"&
 IF(ISERROR(MATCH("Tuesday",A:A,0)),"N","Y")&"/"&
 IF(ISERROR(MATCH("Wednesday",A:A,0)),"N","Y")&"/"&
 IF(ISERROR(MATCH("Thursday",A:A,0)),"N","Y")&"/"&
 IF(ISERROR(MATCH("Friday",A:A,0)),"N","Y")&"/"&
 IF(ISERROR(MATCH("Saturday",A:A,0)),"N","Y")

If you have to search within cells like "Monday,tuesday" you can use this formula. THIS IS AN ARRAY FORMULA THAT YOU MUST HIT CTL SHFT ENTER to make work.

=IF(OR(NOT(ISERROR(FIND("Sunday",A:A,1)))),"Y","N")&"/"&
IF(OR(NOT(ISERROR(FIND("Monday",A:A,1)))),"Y","N")&"/"&
IF(OR(NOT(ISERROR(FIND("Tuesday",A:A,1)))),"Y","N")&"/"&
IF(OR(NOT(ISERROR(FIND("Wednesday",A:A,1)))),"Y","N")&"/"&
IF(OR(NOT(ISERROR(FIND("Thursday",A:A,1)))),"Y","N")&"/"&
IF(OR(NOT(ISERROR(FIND("Friday",A:A,1)))),"Y","N")&"/"&
IF(OR(NOT(ISERROR(FIND("Saturday",A:A,1)))),"Y","N")&"/"
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • This will not work on the cells that have multiple in one cell that is comma delineated, like the OP stated in the comments. – Scott Craner Aug 09 '18 at 23:01
  • if the user has office 365 Excel: `=TEXTJOIN("/",,IF(ISNUMBER(FIND(TEXT(ROW($1:$7),"dddd"),E1)),"Y","N"))` as an array formula. – Scott Craner Aug 09 '18 at 23:03
  • Oh jeez, is' that what he's saying?? All right, i'll make an array no vba solution... – pgSystemTester Aug 09 '18 at 23:06
  • I am not on speaking terms with the Text formula combined with dates. https://stackoverflow.com/questions/48155382/dynamically-display-weekday-names-in-native-excel-language – pgSystemTester Aug 09 '18 at 23:13
  • It has to be used in the array, and I do not like having to type the days out long hand, so I use it so I can copy paste and just change the number. – Scott Craner Aug 09 '18 at 23:15
  • 1
    @ScottCraner yeah that does make some sense. I'm leaving my answer as it stands for now. Maybe someobody will someday want to do an array search and give me a – pgSystemTester Aug 09 '18 at 23:17