Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel Select Case(Range) is it possible?

Posted on 2012-08-24
Medium Priority
1,062 Views
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
``````

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
0
Question by:MarkVrenken
• 4
• 3

LVL 18

Expert Comment

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

LVL 18

Assisted Solution

xtermie earned 2000 total points
ID: 38329091
also note that the syntax for the SUMIFS function is:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)
Note: Up to 127 Criteria_range / Criteria pairs can be specified in the function.

So in your case you should have the Sum_range, and Criteria range and not pass the function as the criteria range.

Insert a column and use your QuarterToMonth function to convert values and then use that range as the Criteria range.
0

LVL 1

Author Comment

ID: 38329110
This is sort of what i want
Book1Testing.xlsx
0

LVL 1

Author Comment

ID: 38329126
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.
0

LVL 18

Accepted Solution

xtermie earned 2000 total points
ID: 38329143
You get the data from a database export, correct?
Then you need to calculate in another column (that exists but is empty I assume).

You can insert new column as the intermediate step to get your data calculated and before updated (import back to db) you can copy-paste the calucation column as values (so as not to import formulas) and delete the intermediate column.

I assume since you do this in an Excel file, there is no need to change the structure of the DB. You are using excel to calculate the value of a field in a table that is originally empty.  Correct?
0

LVL 1

Author Comment

ID: 38329165
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.
0

LVL 18

Expert Comment

ID: 38329173
Good luck, and thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
###### Suggested Courses
Course of the Month15 days, 23 hours left to enroll