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
7
Medium Priority
?
1,062 Views
Last Modified: 2012-08-24
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
0
Comment
Question by:MarkVrenken
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

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

Assisted Solution

by:xtermie
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

by:MarkVrenken
ID: 38329110
This is sort of what i want
Book1Testing.xlsx
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Comment

by:MarkVrenken
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

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

by:MarkVrenken
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

by:xtermie
ID: 38329173
Good luck, and thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question