asked on

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

PeterBaileyUk

ASKER

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"

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

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

Saqib Husain

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")