Showing posts with label Select Case. Show all posts
Showing posts with label Select Case. Show all posts

Wednesday, July 23, 2014

Using VBA to Reformat a Report

This post is going to be somewhat short and sweet - I'm not going to explain every little part, but I will provide a demo file for you to play with.

The Problem:
A report from a 3rd party vendor can only be exported to Microsoft Word is very pretty and in a table-like format.  However, each individual data point was contained in what appeared to be a textbox. Therefore, when attempting to copy paste the results into excel, all headers, footers, and data points are in their own cell, but in the same column.

Example of pasting report in cell A1:
**Note: I have changed ALL of the data and this report does NOT reflect anything real.

The Solution:
My mind immediately went to VBA for the solution this time.  Here are a few concepts that you need to know about to accomplish this task:
  • Option Explicit - always use this to ensure all variables are declared
  • Screen Updating - I use this to keep the screen from flashing when switching sheets and to make the macro run faster
  • Do until Loop - This is used to step down the worksheet row-by-row until a certain condition is met
  • Select Case - This is like a big IF/Then statement. 

When I create a "Formatting" macro, I like to have one spreadsheet for the original "pasted" format, and worksheet for the result after the macro runs.  This allows you to quickly, visually compare original vs new to validate accuracy of your code. 

The Code:

Simplified Explanation:  The macro looks at each populated row looking for the department name.  Once found, it gathers the information for that departments encounter and then places it in a more tabular format on the "Final" worksheet. It will do this until the "LastRow" (assigned by counting the populated rows) is evaluated.

Option Explicit

Sub Reorganize()
Dim row As Variant, lastrow As Variant, Dept As Variant, Isle As Variant, ID As Variant, CartoonAccount As Variant, CartoonName As Variant, Order As Variant, Row1 As Integer

Application.ScreenUpdating = False
Sheets("Paste").Activate

lastrow = ActiveSheet.UsedRange.Rows.Count
row = 1

Do Until row = lastrow
    If row >= lastrow Then Exit Do
    Select Case Cells(row, 1).Value
        Case Is = "Health"
            Dept = Cells(row, 1).Value
            Isle = Cells(row + 1, 1).Value
            ID = Cells(row + 2, 1).Value
            Order = Cells(row + 3, 1).Value
            CartoonName = Cells(row + 4, 1).Value
            CartoonAccount = Cells(row + 5, 1).Value
                
                    Sheets("Final").Activate
                        Row1 = 2
                        Do Until Cells(Row1, 1).Value = ""
                            If Cells(Row1, 1).Value = "" Then Exit Do
                            Row1 = Row1 + 1
                        Loop
                        
                        Sheets("Final").Cells(Row1, 1).Value = Dept
                        Sheets("Final").Cells(Row1, 2).Value = Isle
                        Sheets("Final").Cells(Row1, 3).Value = ID
                        Sheets("Final").Cells(Row1, 4).Value = Order
                        Sheets("Final").Cells(Row1, 5).Value = CartoonName
                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount
                        Row1 = 2
                        Sheets("Paste").Activate
                        row = row + 1
                                     
        Case Is = "FurnitureDept"
            Dept = Cells(row, 1).Value
            Isle = Cells(row + 1, 1).Value
            ID = Cells(row + 2, 1).Value
            Order = Cells(row + 3, 1).Value
            CartoonName = Cells(row + 4, 1).Value
            CartoonAccount = Cells(row + 5, 1).Value
                
                    Sheets("Final").Activate
                        Row1 = 2
                        Do Until Cells(Row1, 1).Value = ""
                            If Cells(Row1, 1).Value = "" Then Exit Do
                            Row1 = Row1 + 1
                        Loop
                        
                        Sheets("Final").Cells(Row1, 1).Value = Dept
                        Sheets("Final").Cells(Row1, 2).Value = Isle
                        Sheets("Final").Cells(Row1, 3).Value = ID
                        Sheets("Final").Cells(Row1, 4).Value = Order
                        Sheets("Final").Cells(Row1, 5).Value = CartoonName
                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount
                        Row1 = 2
                        Sheets("Paste").Activate
                        row = row + 1
        Case Is = "Toys"
            Dept = Cells(row, 1).Value
            Isle = Cells(row + 1, 1).Value
            ID = Cells(row + 2, 1).Value
            Order = Cells(row + 3, 1).Value
            CartoonName = Cells(row + 4, 1).Value
            CartoonAccount = Cells(row + 5, 1).Value
                
                    Sheets("Final").Activate
                        Row1 = 2
                        Do Until Cells(Row1, 1).Value = ""
                            If Cells(Row1, 1).Value = "" Then Exit Do
                            Row1 = Row1 + 1
                        Loop
                        
                        Sheets("Final").Cells(Row1, 1).Value = Dept
                        Sheets("Final").Cells(Row1, 2).Value = Isle
                        Sheets("Final").Cells(Row1, 3).Value = ID
                        Sheets("Final").Cells(Row1, 4).Value = Order
                        Sheets("Final").Cells(Row1, 5).Value = CartoonName
                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount
                        Row1 = 2
                        Sheets("Paste").Activate
                        row = row + 1
        Case Else
            row = row + 1
    End Select

Loop

Application.ScreenUpdating = True
Sheets("Final").Activate

End Sub


I LOVE VBA - What can I say, it was my first programming language.. I'm self-taught so if you are an expert and have a better way to accomplish this, feel free to contribute to the conversation!  Again, we're here to learn together- from each other.  

Demo File (Does Contain Macros!): 

Monday, January 6, 2014

VBA: Select Case

Alright VBA users... Have you ever needed to to use and if/then statement with multiple criteria and need to run a lot of code after the criteria is met?  Well, Select Case is the code for you!

It looks like this:

Select Case [What you are evaluating]
   Case is = [potential value]
       'your code goes here
   Case is = [potential value]
       'your code goes here
   Case is = [potential value]
       'your code goes here
   Case Else
       'your code goes here
End Select

Let's put it to use.  In this example, I am going to evaluate the value within cell B1.  In my code, I personally prefer using the "Cells()" reference method over the "Range()" reference method.  Cells(row, col) is what you'll see.

Select Case Sheets("Sheet1").cells(1, 2).value - This opens the "Select Case" Statement and shows that we're looking at the value of cell B1 or row1, column2.

Next, if the cell value is 1, 2, or 3 I want run specific code depending on the value.
Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]

So what if the cell value isn't 1-3?  Well, in that "case" (Pun!) you will use the "Case Else" Statement.

Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]
   Case Else
      [insert code here]

Finally, when you are done adding cases and code, you need to close the Select Case Statement with "End Select"

Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]
   Case Else
      [insert code here]
End Select

**Tips and tricks:
#1 - if you are going to be using VBA, pay attention to the formatting of the code.  Think of your code as an onion (multiple layers, not that they stink or make you cry).  Your opening statement is the outer layer (no indentation).  As you move into the statement - for example from the "Select Case ..." to "Case is =..." you will press tab and indent to indicate that what is happening is happening within the Select Case Statement. The code you include if a statement is True would be indented twice.  The "End Select" statement is referring to the "outer layer" so it is not indented to complete visual.

My code above in Blue is an example of this.

#2 - If you do not intend for anything to happen when a condition is met using the Select Case statement, simply do not place any code in the [insert code here] section and the program will resume by ending the select statement and continuing to the next line.