Help with Select Case

[kakiebos]

Hi All, I need help with a Select Case in my code. I'm new to VBA and have never used the Select Case function. With my online research, I found some examples, but none is really giving me the information that I need.

When the user call a UserForm (frmResource) from any one of the sheets Monday to Sunday a label (lDay) in the form indicate to the user which day they are in. The information that they enter in this UserForm is then sent to that day's sheet. Some of this information is then captured on a weekly summary sheet (Object name: shSummary). In the weekly summary I want to add the date to a certain position, depending on the day of the week.

I was thinking that the Select Case code would do the job the best, but something is not working for me.
Sometimes it will add the date for Saturday on the Monday position in the weekly summary

Can someone give me some insight as to why it is not working as expected.

For additional information, I prefer to use the sheets object name in my code and each of the daily sheets is called as shMon; shTue etc.

Dim shSum As Worksheet
Dim Day As Integer


'Add date to summary sheet
    Select Case Day
      Case Me.lDay = "Monday"
        shSum.Range("B12").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
      Case Me.lDay = "Tuesday"
        shSum.Range("B14").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
      Case Me.lDay = "Wednesday"
        shSum.Range("B16").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
      Case Me.lDay = "Thursday"
        shSum.Range("B18").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
      Case Me.lDay = "Friday"
        shSum.Range("B20").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
      Case Me.lDay = "Saturday"
        shSum.Range("B22").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
      Case Me.lDay = "Sunday"
        shSum.Range("B24").Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))
    End Select

 

[steve the fish]

Do you mean something like this instead? Notice the use of LCase so make all your days lower case.

Select Case LCase(Me.lDay)
    Case "monday"
        'Do this
    Case "tuesday"
        'Do that
End Select

 

[steve the fish]

You could also see if this does the same thing:

dayInt = Application.Match(Me.lDay, Array("monday", "tuesday", "wednesday", "thursday", "friday", "saturday", "sunday"), 0)
shSum.Range("B" & 2 * dayInt + 10).Value = CStr(Format(Me.lDate, "yyyy-mm-dd"))

 

[kakiebos]

Hi Steve.

Thank you for your assistance. The first set of code was not working, I think it has something to do with my code to enter the date in the summary sheet. Perhaps I did not have my syntax correct. I will still look into that.

Your second code that you gave me is working perfectly and looks much neater than the code that I had in mind. Just to confirm dayInt, do I dim it as Array?

 

[steve the fish]

No it's an integer. Match will produce the position in the array. 1 for monday 2 for tuesday etc.