asked on

Excel Select Case(Range) is it possible?

Dear experts,
I wrote this piece of code to convert values to numbers. but the problem is i want to input a range of values so that i can compare them with the month numbers in my sheet.

this is my select case

Function QuarterToMonth(QuarterIn As String) As Integer


Select Case QuarterIn

Case "Q1"
     QuarterToMonth = 1
Case "Q2"
    QuarterToMonth = 4
Case "Q3"
    QuarterToMonth = 7
Case "Q4"
    QuarterToMonth = 10
    
Case "H1"
    QuarterToMonth = 1
Case "H2"
    QuarterToMonth = 7
    
Case "jan"
    QuarterToMonth = 1
Case "feb"
    QuarterToMonth = 2
Case "Mar"
    QuarterToMonth = 3
Case "Apr"
    QuarterToMonth = 4
Case "May"
    QuarterToMonth = 5
Case "Jun"
    QuarterToMonth = 6
Case "Jul"
    QuarterToMonth = 7
Case "Aug"
    QuarterToMonth = 8
Case "Sep"
    QuarterToMonth = 9
Case "Oct"
    QuarterToMonth = 10
Case "Nov"
    QuarterToMonth = 11
Case "Dec"
    QuarterToMonth = 12
End Select


End Function

Open in new window

What i would like to insert is a range of values like Sumifs(M:M,QuarterToMonth(K:K),1) . but i get a #Value error. Does anyone have a suggestion?

Best regards,

Mark

Anastasia D. Gavanas

Can we have a sample of the file and an expected results a bit more detailed?

SOLUTION

Anastasia D. Gavanas

membership

This solution is only available to members.

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

Start Free Trial

MarkVrenken

ASKER

This is sort of what i want
Book1Testing.xlsx

MarkVrenken

ASKER

I can make that extra column but the table is imported from a database. Is it possible to import a database and when a extra column is put in to the database, on update, that the calculated column will still exist? I'm not allowed to change the database structure. so i can't change the database.

ASKER CERTIFIED SOLUTION

Anastasia D. Gavanas

membership

This solution is only available to members.

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

Start Free Trial

MarkVrenken

ASKER

yes you're assumptions are correct. I will try it this way. It's a shame it can't be done directly. but thanks, i will mark your answer as solution.

Anastasia D. Gavanas

Good luck, and thanks!