VBA Code to clear current region

ThisWorkbook.Worksheets("Sheet2").Range("A6").CurrentRegion.Clear

Code to display header from Database

For i = 0 To RS.Fields.Count - 1
ThisWorkbook.Worksheets("Sheet2").Cells(6, i + 1) = RS.Fields(i).Name
Next i

2009 Calendar

What is the best way to create a calendar? Using a program you purchased from the internet, Get a free template from a website or key the dates in manually into an Excel spreadsheet?

How about having a template that has already been done up and all you have to do is to key in the year and the dates are populated automatically into the worksheet. You can add in the public holidays of your choice, even your own leave calendar and have it highlight auotmatically in the calendar. All this without the use of any programs, macros. Just by using formuals. If you think that this is a calendar that will meet your needs, go to this excel calendar page to download the template.

Free Excel Calendar

I have an Excel Calendar Template that could show the public holdiays of any country simply by adding the dates into the worksheet. A good tool to plan for your activities in year 2009. You can click on the link for more info http://www.everydayexcel.com/excel_calendar.php.

Cheers.
Jason Khoo

Discount Rate

I think in calculating PV or NPV, one of the most confusing part of the calculation is the determination of the discount rate. I used to think that discount rate is the interest rate. Now I have come to realise that it is not the case. Discount rate is the rate you use to determine whether a project is worthwhile taking. Discount rate takes into consideration the risk of the project. Take a look at this Net Present Value example and see if it could help to clarify the difference between discount rate and interest rate.

3 Important Attributes of an Excellent Budgeting Tool

The 3 important attributes of an Excellent Budgeting Tool are:

1. Total Control of Template Layout and Easy Customisation
2. Versatile Analysis of Data in Different Business Perspective
3. Easy Consolidation of Data and Generation of High-Quality Reports

Find out more from this budgeting tool write-up.

Excel 2007 Conditional formatting bug

I was developing my Excel 2007 eCourse and trying out the conditional formatting that I stumble upon this bug. Maybe it's not, I don't know where to report it so I decided to publish this in my blog to see if somebody would like to provide an answer.

In the conditional formating bug file, take a look at cell B4. It is supposed to be red based on the condition set but it turns out green. Anybody can help?

Learn more about Excel 2007

How do you like the last post on Excel 2007? If you like it and would like to have more of it, I have good news for you. I am creating a brand new eCourse for you to familarise yourself with Excel 2007 and its FREE! If you interested, click onto this free Excel 2007 eCourse page.

How to activate Save As in Excel 2007

Hi,

If you are using Excel 2007 for the very first time, you might be stumped on where the save as function is. Because you will not be able to find the familar File Menu you used to see in Excel 2003 and below. So if you are ready to upgrade 2007, you might want to read this first so that you would not be caught off-guard. In here, we will show you the different ways you could save a file:

If you are using short cut keys, I have good news for you. All the short cut keys you learnt in the previous version applies in version 2007. So you would have no problem at all. For those who want to know the shortcut key to save as, it is function F12 or Ctrl + F2.


If you are used to the file menu, you can activate the save as function by clicking on the icon at the top left hand corner of Excel. See the picture below. In that, you will see the all familar list of functions when you click on the file menu in the previous version of Excel.



That's it for the Save As Function in Excel 2007. Do come back reguarly for new updates.

A new Life with Excel Budgeting

Are you going to start your annual budgeting exercise soon?

Are you getting ready to

• Protect the template to minmise the disruption to your consolidation effort?
• Check the templates filled up by the business heads in detail, making sure that the template layout is not changed in any way?
• Put the budget number in one single workbook so that you could consolidate the numbers by summing across the worksheets?
• Set up links to analyse the key expenses by departments, country, by month?
• Create links to another workbook for the executive summay and charts for you reporting?
• Create multiple sets of report for different managers, e.g product manager, channel manager, etc?
• Work overtime and during the weekends to get the budget out before the deadline?
• Run a few revisions of the budget and repeat the process for each revision?

If you are, I have GOOD NEWS for you!........More on Excel Budgeting

Does this describe your excel budgeting situation?

Mega Retailer Company (“MRC”) is one of the leading global distributors of consumer products. Their products are categorized mainly into 3 product groups, Food, Personal Care and Home Care. Its customer base ranges from Hotels/Restaurants to Supermarkets to your neighbourhood provision shop. MRC has local presence in every country in Asia Pacific. One or more companies are set up in each country to serve the local market.

You are the Budgeting Manager for MRC. It’s budget time again! :(

Am I going to go thru the same old process again? Sending the templates......

If you don't want to, why not take a look at this brand new way of budgeting?

Improved Find Function for use in VBA

Below is the improved find function which aske users for area to find and also choose whether they would like the function to return the cell address, row or column of the cell returns by the find function

Function Find_Address(What_2_Find, Where_2_Find, _
GetAddress_Row_or_Column As String)

' this is a range.
Value_or_Formula = xlFormulas
    'xlVaues - search the text/value in the cell,
        'for cells with formula, it will look at the result.
    'xlformulas - search the text/value within formula
    'xlformulas works even when cell is hidden.
    'It is able to look for value too.
Exact_Partial = xlPart
    'xlPart - find cells that contains (What_2_Find)
    'xlWhole - find cells which contains exactly the value
        'placed in (What_2_Find)
        
Match_Capital_Letters = False
    'False - when A and a is treated the same
    ' True when A and a means different things.
    
With Where_2_Find
    Set c = .Find(What:=What_2_Find, LookIn:=Value_or_Formula, _
        LookAt:=Exact_Partial, MatchCase:=Match_Capital_Letters)
    'c is the cell that meet your find criteria
    If Not c Is Nothing Then
        
        Select Case GetAddress_Row_or_Column
            Case Is = "Address"
                Find_Address = c.Address
                    ' Change this to c.column if you want
                        'the column
                    ' where the text is found.
                    'change it to c.address if you want the
                        'address returned.
            Case Is = "Row"
                Find_Address = c.Row
            Case Is = "Column"
                Find_Address = c.Column
        End Select
    Else
        Find_Address = "A1" ' Cannot find, so default
                                'the address to A1
    End If
End With

End Function

Several Hours vs One Minute

You are given a 10,000 lines product listing containing the product code and product description all combined into one column, and asked to separate the product code from the product description. Normally, with just basic Excel knowledge, you need to spend hours going through the 10,000 rows of records and type out the product code which can be 3 letters or 5 letters. Now take this test, find out how much time you need to spend on a sample of 10 records, then multiply your time by 1,000 times. This would be the time you need to spend to get this job done.

With some knowledge of Excel worksheet functions, you can use a formula to separate the 3-letter product codes from the list. But, you still need to take an hour or two to go through all the records to identify all the 5-letter product codes so that you could adjust your formula for the 5-letter product codes one by one.

Now, in our Excel training course, you can learn to apply one more formula that can help separate the product code from the product description intelligently. And the time you needed? One minute - and this is the time taken to set up the formula.

Do you know the 2 formulas you need to save you several hours on the task?

If you want to know the answer and you are not in Singapore, you can find out the answer and also learn how to work more efficiently with Excel using our online "Unleashing the Potential of Excel" course.

Macro to find cell format

This macro will search through the range and identify the cell format (in our example, it is the cell color) and copy and paste the formulas from the first row to the last row which is 100

Sub Find_Cell_Format()

Set Which_Worksheet_2_Find = ThisWorkbook.Worksheets("Details")
Set Range_2_Find = Which_Worksheet_2_Find.Range("C28:BZ28")
        ' this is a range to find.
last_row_in_the_template = 100 ' Change the number of the last
     ' accordingly

Paste_Type = xlPasteFormulas
            'xlPasteAll 'default
            'xlPasteAllExceptBorders
            'xlPasteColumnWidths
            'xlPasteComments
            'xlPasteFormats
            'xlPasteFormulas
            'xlPasteFormulasAndNumberFormats
            'xlPasteValidation
            'xlPasteValues
            'xlPasteValuesAndNumberFormats
Plus_Minus_Times_Divide = xlPasteSpecialOperationNone
            'xlPasteSpecialOperationAdd
            'xlPasteSpecialOperationDivide
            'xlPasteSpecialOperationMultiply
            'xlPasteSpecialOperationNone Default
            'xlPasteSpecialOperationSubtract

For Each c In Range_2_Find '[A1:C5]
    If c.Interior.ColorIndex = 37 Then '37 refers to color
    'orange in grid chart
        c_col = c.Column
        Which_Worksheet_2_Find.Cells(29, c_col).Copy 'copy the content in row 29
        Which_Worksheet_2_Find.Range( _
            Which_Worksheet_2_Find.Cells(30, c_col), _
            Which_Worksheet_2_Find.Cells(last_row_in_the_template, c_col)) _
            .PasteSpecial Paste:=Paste_Type, Operation:=Plus_Minus_Times_Divide
        CutCopyMode = False
        Range("A1").Select
    End If

Next

End Sub

Macro for the find function

This macro will return the location (row, column or address) of the cell containing the search value.

Function Find_Row_Num(What_2_Find)

What_2_Find = "Insert row before this line for PAPDR"
Set Which_Worksheet_2_Find = ThisWorkbook.Worksheets("Details")
Set Range_2_Find = Which_Worksheet_2_Find.Range("C30:C65000") ' this is a range.
Value_or_Formula = xlFormulas 'xlVaues - search the text/value in the cell,
                        'for cells with formula, it will look at the result.
                        'xlformulas - search the text/value within formula
                        'xlformulas works even when cell is hidden.
                        'It is able to look for value too.
Exact_Partial = xlPart 'xlPart - find cells that contains (What_2_Find)
                        'xlWhole - find cells which contains
                        'exactly the value placed in (What_2_Find)
Match_Capital_Letters = False 'False - when A and a is treated the same
                               ' True when A and a means different things.


With Range_2_Find
    Set c = .Find(What:=What_2_Find, LookIn:=Value_or_Formula, _
        LookAt:=Exact_Partial, MatchCase:=Match_Capital_Letters)
    'c is the cell that meet your find criteria
    If Not c Is Nothing Then
        Find_Row_Num = c.Row ' Change this to c.column if you want the column
                             ' where the text is found.
                             'change it to c.address if you want the address returned.
    Else
        Find_Row_Num = 1 ' Cannot find, so default the address to A1
    End If
End With

End Function

Consolidate your data in your workbook with this addin

In may have sounded absurd 2 years ago. But as I continue to train others in my Advanced Excel Course, the idea has turned into a reality. It started off with using MSQuery to consolidate the data manually from different worksheets which I still offer as a topic for users to perform their consolidation and then to prepare reports using Pivot Table. Now, I have developed an add-in that could consolidate the data in seconds and start off with an empty pivot table so that users could prepare the reports based on their requirements. The Addin is free at this moment as I would like to collect some feedback on how to improve it further. So if you are keen, go to this page which will demo how to consolidate salary information for preparing income tax returns form. It will also tell you where to get the free download.

Remove Duplicates

Excel has a number of ways to remove duplicates. One of them is to use Advanced Filter and another is using the IF formula. Advanced Filter is a quick way to get it done but it has its constraints. The IF formula is a more practical approach based on the scenarios described by our participants in our Advanced Excel Course. Find out how to remove duplicates using both methods here and also the detailed differences between the two.

A revolutionary solution to Excel Budgeting for corporations

Greatings to you for the New Year 2008!

I feels good to write this entry. After one month of hard work, I have finally produced the 2 articles that provide you with the details of how our revolutionary solution to Excel budgeting is likely to cause a stir in the industry. With this solution, you can finally put your mind at ease when it comes to budgeting. No more sleepless nights to churn out the final output! Not more external links that will cause you to worry that the numbers might not get updated. And a chance to produce quality analysis and prepare all the materials you need to get your budget approved. You can also put off any plans to invest in a Business Performance Management System and still save on extra manpower you need to manage the budget. The solution uses a combination of formulas and functions such as the pivot table and msquery. To read more, read the following 2 articles:

The benefits of Excel budgeting
Our revolutionary approach to Excel Budgeting for corporations

Cheers!