Help with Select Case

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
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.
Code:
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,882
Office Version
365
Platform
Windows
Do you mean something like this instead? Notice the use of LCase so make all your days lower case.

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,882
Office Version
365
Platform
Windows
You could also see if this does the same thing:

Code:
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

Board Regular
Joined
Jun 10, 2018
Messages
59
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,882
Office Version
365
Platform
Windows
No it's an integer. Match will produce the position in the array. 1 for monday 2 for tuesday etc.
 

Forum statistics

Threads
1,089,422
Messages
5,408,139
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top