Faysal Easy Excel

Loops are great tool in Excel VBA to repeat your actions. If I tell you a simple example about loop then it would be better for you to understand. I believe that you have already worked with some WorksheetFunction like SUMIFS, INDEX-MATCH, COUNTIFS, SUM etc. And few times you did not locked the Lookup_Value or Criteria due to you wish to use the same function for multiple times but different lookup_values and criteria. In Excel VBA Loops are great tool to repeat your actions how many times you have set. While you always need the same command on different cells with a specific cell difference, then you can use Loop. It will reduce your work and time and give the result faster. Types of Loops There are different types of Loops in Excel VBA. Based on your task you have to decide which Loop will match better for your task. In Excel VBA There are 5 different types of Loops are available. These are: 1. For ... Next 2. For ... Each ... Next 3. Do ... While 4. Do ... Until 5. Wh

Doughnut Chart is a cool chart to show the progress status like an analog speed meter. In my opinion this chart is great to show a single information which is very important to Management Team. But you can also show many information in a single Doughnut Chart . Assume that, you are a Pilot of Mig29 . In cockpit and need to know the fuel status immediately. Just look at the Fuel Meter for 1 second and you will know how much fuel is present in your stock. For the perspective of Bangladesh Mobile Phone Industry , most of the company always focused 3 major points. Days gone in the current month What is the status of total volume achievement against total volume target What is the value achievement status against target. To make the Daily Sales Report , which shows the above 3 information the best way is to show it in Doughnut Chart . Below image is showing a MTD (Month to Date) Sales Status of the current month based on sample data. Image 1: Doughnut Chart Ho

For best understanding you need to practice with real data problem. From this point of view, I'm going to solve a String problem from real life experience. Problem: The problem is a raw data after downloading from data server, the SKU (Stock Keeping Unit) code wrongly encoded as "SKU-US-90/10/45". To make a SKU wise Stock Report you need to convert the SKU code to correct format like below image: Image 1: RawData SKU and Corrected SKU Solution: The problems are: Converting US to USA "/" to "" "-" to "" Use the below code to convert all the wrong SKU to correct SKU: Sub CorSKU () Dim x As String Dim i As Integer For i = 2 To 8 x = Cells(i, 1 ).Value Cells(i, 3 ).Value = Replace(Replace(Replace(x, "US" , "USA" ), "/" , "" ), "-" , "" ) Next i End Sub Here is the result: Image 2: Result got from VBA

String is an important variable type. I will try to discuss here about it. As the 'String' name suggest, it is used to hold strings of text. You have to work with Strings of text in Excel VBA a lot in next. So, it's time to learn in depth about it. I know now you will ask your mind, wait a minute, what things should you know about simple String variable? Don't it just store TEXT values? So, why "depth"? Well, my answer is Yes. You are absolutely right. But in "depth" I mean some others option related to String, must know for better work. Simply use a Dim command to store a String Variable like below: Dim x as String And now while you are going to store a variable against the String variable simply use Double Quotes ("") mark like below: Dim x as string x = "This is String text" If you set a Date or Number or any others variable within Double Quotes ("") against a String variable, it definitely NOT s

WITH is another useful statement in Excel VBA. It usually used to ignore using the same object name for changing various aspects. For example if you would like to change the (1) font color (2) font size (3) font italic etc. then you simply write the codes like below: Cells( 4 , 5 ).Font.Color = RGB( 254 , 00 , 00 ) Cells( 4 , 5 ).Font.Size = 12 Cells( 4 , 5 ).Font.Italic = True But this would be more easier if you use WITH statement like below, where Cells(4,5).Font used only once. You then type the property you need after a dot: With Cells( 4 , 5 ).Font    .Color = RGB( 254 , 00 , 00 )    .Size = 12    .Italic = True End With With Statements are quite intuitive, so we don't really need to say too much about them. But just remember: if you're typing the same object over and over, you might do better to use a With ... End With statement. Writing with WITH statement the VBA code will run faster due to it will not read the same object again and again

SELECT CASE is another statement for decision making while you have 3 or more condition. Definitely you can use IF statement instead of SELECT CASE statement. The convenient thing is that of applying the SELECT CASE is, it makes the macro reading capability faster than IF statement. Rather than using IF statements, SELECT CASE statement is very clear in logical operation. It supports nested condition same as IF statement. In others programming language SELECT CASE statement is also known as SWITCH Statement. Example 1: This is simple example of SELECT Case statement. The working process is as below: Image 1: Simple Select Case Statement Now write the below codes in your module and run it: Sub selcase1 ()        Dim x As String        Dim y As String          x = "Microsoft"        y = "Excel"        Select Case x = y               Case True                      MsgBox "This is True"               Case False              

In this example you will learn about the cell alignment in Excel VBA. The Problem: Assume that, We have a table that shows, Mobile Brand Name, and selling quantity as below: Image 1: Sample data What you need to do is, create a Grade column and show the grade using IF statement and put a VBA Button. Also add the cell alignment and full row background color based on grade. The Grade scale is: If above 90000, A+, 70000, A, 50000, B+, 30000, B, 10000, C+, F. The Solution: Design the table like below: Image 2: Data Table Now in Module write the below codes: Option Explicit    Sub grade_analysis ()       Dim x As Integer       Dim y As Long       Dim z As String       For x = 2 To 9          y = Cells(x, 2 ).Value          If y >= 90000 Then         Cells(x, 3 ).Value = "A+"         Cells(x, 3 ).HorizontalAlignment = xlCenter             Cells(x, 3 ).Interior.Color = RGB( 255 , 155 , 250 )          ElseIf y >