encode month and category in excel vba

I have a range of cells for a client that represent 12 months with 11 rows each representing a category.

so b16 to m26 represents a whole years worth of monthly data against each category.

I want to do an event on the cell in the range that is clicked and I have setup the code to do that.

as the 11 categories and months stay the same i wonder if there is an easier way to get to my event code without trawling through a long select case:

    Select Case Target.Address
        'jan
        Case "$b$16"
        'event for category overall jan
        Case "$b$17"
        'event for category smmt jan
        Case "$b$18"
        Case "$b$19"
        Case "$b$20"
        Case "$b$21"
        Case "$b$22"
        Case "$b$23"
        Case "$b$24"
        Case "$b$25"
        Case "$b$26"
        
        
        'feb
        Case "$c$16"
         'event for category overall feb
        Case "$c$17"
        
        Case "$c$18"
        Case "$c$19"
        Case "$c$20"
        Case "$c$21"
        Case "$c$22"
        Case "$c$23"
        Case "$c$24"
        Case "$c$25"
        Case "$c$26"

Open in new window

ee.PNG
PeterBaileyUkAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
From the question I thought maybe you can use something like

Month = Target.Address - 1
cat = Cells(Target.Row, 2)

I am not sure what you are trying to explain in the follow up. It would be helpful if you upload a sample excel file (possibly with fake values).
0
 
PeterBaileyUkAuthor Commented:
I laid something out like this:
Const Others As String = "25, 26"
Const Lcv As String = "23, 24"
Const Bikes As String = "21, 22"
Const Car As String = "19, 20"
Const CW As String = "18, 20, 22, 24, 26"
Const SMMT As String = "17, 19, 21, 23, 25"
0
 
PeterBaileyUkAuthor Commented:
I am trying to determine from the cell column and row what category and month the code fits into:

I did a bit more:
I am just moving onto vehicle type now

i cannot see any more efficient way this way stops me having to create code for each cell


    

        If Mid(Target.Address, 2, 1) = "B" Then SelectedMonth = #1/1/2012#
        If Mid(Target.Address, 2, 1) = "C" Then SelectedMonth = #1/2/2012#
        If Mid(Target.Address, 2, 1) = "D" Then SelectedMonth = #1/3/2012#
        If Mid(Target.Address, 2, 1) = "E" Then SelectedMonth = #1/4/2012#
        If Mid(Target.Address, 2, 1) = "F" Then SelectedMonth = #1/5/2012#
        If Mid(Target.Address, 2, 1) = "G" Then SelectedMonth = #1/6/2012#
        If Mid(Target.Address, 2, 1) = "H" Then SelectedMonth = #1/7/2012#
        If Mid(Target.Address, 2, 1) = "I" Then SelectedMonth = #1/8/2012#
        If Mid(Target.Address, 2, 1) = "J" Then SelectedMonth = #1/9/2012#
        If Mid(Target.Address, 2, 1) = "K" Then SelectedMonth = #1/10/2012#
        If Mid(Target.Address, 2, 1) = "L" Then SelectedMonth = #1/11/2012#
        If Mid(Target.Address, 2, 1) = "M" Then SelectedMonth = #1/12/2012#
    
        If InStr(CW, Mid(Target.Address, 4, Len(Target.Address) - 4 + 1)) <> 0 Then codetype = "CW"
        If InStr(SMMT, Mid(Target.Address, 4, Len(Target.Address) - 4 + 1)) <> 0 Then codetype = "SMMT"

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
You do not have to do that. Simple replace the lines 3 to 24 with

If Target.Column < 14 And Target.Column > 1 Then SelectedMonth = DateValue("1/" & Target.Column & "/2012")
0
All Courses

From novice to tech pro — start learning today.