VBA Macro to Process Google Keywords CSV file into a usable, clickable Excel file

VBA CODE IS FOUND HERE
http://newyorkinteractivemedia.com/training_class_blog/index.php/2011/12/vba-macro-process-google-keywords-csv-file-usable-clickable-excel-file-code/
Google found many keywords in our site from blogs and homework files that were hurting our SEO and making our site less relevant to our specific Keywords such as Excel Training in NYC or VBA Training. By checking where each keyword was getting picked up, we could block that page from being index or delete old files.
This macro separates all the links (up to 7) per line and allows you to click them to check if you should block that page by a robots.txt file.

Excel 2010 VBA to assign a shortcut key

Application.OnKey "^j", "subroutineToRun"

This runs the subroutine named subroutineToRun when CTRL+j is pressed.
The macros must be run once before the shortcut will work.

Macro to close the current worbook without saving and reopen the same workbook

Sub ClosewithoutsaveAndReopen()

'Defines Variables
Dim nameOfCurrentWorkbook As Variant
Dim nameOfCurrentWorkbookPath As Variant

'Finds the name of the current workbook
    nameOfCurrentWorkbook = ActiveWorkbook.Name
'finds the current path of hte workbook
    nameOfCurrentWorkbookPath = ActiveWorkbook.Path
    ActiveWorkbook.Close False
'Reopens the same worbook you just closed
    Workbooks.Open (nameOfCurrentWorkbookPath & "\" & nameOfCurrentWorkbook)
End Sub

Some useful VBA snippets

Unmerge all cells = Cells.UnMerge

Unhide all rows =ActiveSheet.Cells.EntireRow.Hidden = False

Unhide all columns = ActiveSheet.Cells.EntireColumn.Hidden = False


Take off autofilter = ActiveSheet.AutoFilterMode = False

Count how many worksheets in a workbook = ThisWorkbook.Sheets.Count 

Name of activeworksheet = ActiveSheet.Name

Selecting entire Table- Range("TableName[#All]").Select

Selecting Table Columns-  Range("TableName[NameOfColumn]").Select

Find and Replace- Selection.Replace What:="Thing to Replace", Replacement:="What to Replace with", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 

Runs another procedure-  Application.Run "nameofprocedure"

Do something to every worksheet-

Sub everyWorksheet()
Dim wSheet As Worksheet

For Each wSheet In ActiveWorkbook.Worksheets
    MsgBox "This is Worsheet: " & wSheet.Name
Next wSheet

End Sub

Excel 2010 VBA Loops Tutorials

Exercise 1: Do… Loop Until…

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.
On Sheet1 select cell C2 and run the macro Loop1.
Here's the code:
Sub Loop1()
' This loop runs until there is nothing in the next column
    Do
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
This macro places a formula into the active cell, and moves into the next cell down. It uses Loop Until to tell Excel to keep repeating the code until the cell in the adjacent column (column D) is empty. In other words, it will keep on repeating as long as there is something in column D.
Delete the data from cells C2:C20 and ready for the next exercise

Exercise 2: Do While… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.
On Sheet1 select cell C2 and run the macro Loop2
Here's the code
Sub Loop2()
' This loop runs as long as there is something in the next column
    Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub
This macro does the same job as the last one using the same parameters but simply expressing them in a different way. Instead of repeating the code Until something occurs, it does something While something is the case. It uses Do While to tell Excel to keep repeating the code while there is something in the adjacent column as opposed to until there is nothing there. The function IsEmpty = False means "Is Not Empty".
Delete the data from cells C2:C20 and ready for the next exercise

Exercise 3: Do While Not… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.
On Sheet1 select cell C2 and run the macro Loop3.
Here's the code:
Sub Loop3()
' This loop runs as long as there is something in the next column
    Do While Not IsEmpty(ActiveCell.Offset(0, 1))
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub
This macro makes exactly the same decision as the last one but just expresses it in a different way. IsEmpty = False means the same as Not IsEmpty. Sometimes you can't say what you want to say one way so VBA often offers an alternative syntax.
Delete the data from cells C2:C20 and ready for the next exercise

Exercise 4: Including an IF statement

The object of this macro is as before, but without replacing any data that may already be there.
Move to Sheet2, select cell C2 and run the macro Loop4.
Here's the code:
Sub Loop4()
' This loop runs as long as there is something in the next column
' It does not calculate an average if there is already something in the cell
    Do
    If IsEmpty(ActiveCell) Then
        ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
The previous macros take no account of any possible contents that might already be in the cells into which it is placing the calculations. This macro uses an IF statement that tells Excel to write the calculation only if the cell is empty. This prevents any existing data from being overwritten. The line telling Excel to move to the next cell is outside the IF statement because it has to do that anyway.

Exercise 5: Avoiding Errors

This macro takes the IF statement a stage further, and doesn't try to calculate an average of cells that are empty.
First, look at the problem. Move to Sheet3, select cell C2 and run the macro Loop4.
Note that because some of the pairs of cells in columns A and B are empty, the =AVERAGE function throws up a #DIV/0 error (the Average function adds the numbers in the cells then divides by the number of numbers - if there aren't any numbers it tries to divide by zero and you can't do that!).
Delete the contents of cells C2:C6 and C12:C20. Select cell C2 and run the macro Loop5.
Here's the code:
Sub Loop5()
' This loop runs as long as there is something in the next column
' It does not calculate an average if there is already something in the cell
' nor if there is no data to average (to avoid #DIV/0 errors).
    Do
    If IsEmpty(ActiveCell) Then
        If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
            ActiveCell.Value = ""
        Else
            ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
        End If
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
Note that this time there are no error messages because Excel hasn't tried to calculate averages of numbers that aren't there.
In this macro there is a second IF statement inside the one that tells Excel to do something only if the cell is empty. This second IF statement gives excel a choice. Instead of a simple If there is an If and an Else. Here's how Excel reads its instructions…
"If the cell has already got something in, go to the next cell. But if the cell is empty, look at the corresponding cells in columns A an B and if they are both empty, write nothing (""). Otherwise, write the formula in the cell. Then move on to the next cell."

Exercise 6: For… Next Loop

If you know, or can get VBE to find out, how many times to repeat a block of code you can use a For… Next loop.
Move to Sheet4, select cell C2 and run the macro Loop6.
Here's the code:
Sub Loop6()
' This loop repeats for a fixed number of times determined by the number of rows
' in the range
    Dim i AsInteger
    For i = 1 To Selection.CurrentRegion.Rows.Count - 1
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Next i
End Sub
This macro doesn't make use of an adjacent column of cells like the previous ones have done to know when to stop looping. Instead it counts the number of rows in the current range of data and uses the For… Next method to tell Excel to loop that number of times (minus one, because when VBA counts it starts at zero).

Exercise 7: Getting the Reference From Somewhere Else

Select cell G2 and run the macro Loop7.
Here's the code:
Sub Loop7()
' This loop repeats a fixed number of times getting its reference from elsewhere
    Dim i As Integer
    Dim intRowCount As Integer
    intRowCount = Range("A1").CurrentRegion.Rows.Count - 1
    For i = 1 To intRowCount
    ActiveCell.FormulaR1C1 = "=Average(RC[-5],RC[-6])"
    ActiveCell.Offset(1, 0).Select
    Next i
End Sub
You can get the reference for the number of loops from anywhere. This macro places a set of calculations in column G for a number of times dictated by the number of rows in the block of data starting with cell A1. The For… Next statement has been simplified a bit by first declaring a variable intRowCount and filling it with the appropriate information (how many rows in the block by A1). This variable gets used in the next line instead of a long line of code. This is just another example of doing the same job a different way.
If you wanted to construct a loop that always ran a block of code a fixed number of times, you could simply use an expression like:
            For i = 1 To 23

Exercise 8: About Doing Calculations…

All the previous exercises have placed a calculation into a worksheet cell by actually writing a regular Excel function into the cell (and leaving it there) just as if you had typed it yourself. The syntax for this is:
ActiveCell.FormulaR1C1 = “TYPE YOUR FUNCTION HERE”
These macros have been using:
ActiveCell.FormulaR1C1 = “=Average(RC[-5],RC[-6])”
Because this method actually places a function into the cell rather than a value, their results will change as the cells that they refer to change, just like regular functions – because they are regular functions. The calculating gets done in Excel because all that the macro did was to write the function.
If you prefer, you can get the macro to do the calculating and just write the result into the cell. VBA has its own set of functions, but unfortunately AVERAGE isn’t one of them. However, VBA does support many of the commoner Excel functions with its WorksheetFunction method.
On Sheet1 select cell C2 and run the macro Loop1.
Take a look at the cells you just filled in. Each one contains a function, written by the macro.
Now delete the contents from the cells C2:C20, select cell C2 and run the macro Loop8.
Here’s the code:
Sub Loop8()
    Do
    ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _
         ActiveCell.Offset(0, -2).Value)
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

Take a look at the cells you just filled in. This time there’s no function, just the value. All the calculating was done by the macro which then wrote the value into the cell.

Dynamic Range for Pivot Table & Chart Video

From a silly guy

Dynamic Pivot Chart Example

Today, I am going to show, how you can use simple macro code to change the report filter value dynamically.

We will learn how to create the below chart:

How to update Pivot Table Report Filters & Create such a dynamic chart?

To help you understand this technique, I have created a short video. Please watch it below:

[or watch it on YouTube]

Download Example Workbook & Play with it

Go ahead and download the file. It has a demo of this technique + other ideas on Pivot Table Report Filters.

Comparing Lists of Values in Excel using Array Formulas Excel 2010

Comparing List of Values – Different Scenarios

There are 2 scenarios when we compare lists of values.

1. Comparing a list of values with a single value (or condition)
2. Comparing a list of values against another list (array comparison)

Comparing a list of values with a single value (or condition):

This is the most common and easiest comparison. Examples of this are – count of all values > 20, sum of values between 5 and 23, count of employees in purchasing department etc. We have built in formulas in Excel to help us do this easily.

Formula	What it does?
COUNTIF	Counts all the values in a range that meet a criteria. Example: COUNTIF(A1:A10,”>10″) Count of all values in A1:A10 more than 10Help
SUMIF	Sums all the values in a range that meet a criteria Example: SUMIF(A1:A10,”>10″,B1:B10) Sum of all values in B1:B10 where corresponding value in A1:A10 is more than 10Help
COUNTIFS*	Counts all the values in a range that meet multiple criterion Example: COUNTIFS(employees, “a*”,departments, “Purchasing”) Counts the number of employees in Purchasing department whose name starts with letter a.Help
SUMIFS*	Sums all the values in a range that meet multiple criterion Example: SUMIFS(salaries, employees, “a*”,departments, “Purchasing”) Sums up the salary of employees in Purchasing department whose name starts with letter a.Help
SUMPRODUCT	Gives the sum of product of various lists. This formulas is very robust and can be used to compare lists and check against multiple conditions Example: SUMPRODUCT(salaries, departments=”Purchasing”, join_date>datevalue(“1-May-2009″),join_date<=datevalue(“1-May-2011″)) Sums up the salary of employees in Purchasing department who joined between 1-May-2009 and 1-May-2011.Help
AVERAGEIF*	Average of all the values in a range that meet a criteria Example: AVERAGEIF(A1:A10,”>10″,B1:B10) Average of all values in B1:B10 where corresponding value in A1:A10 is more than 10
AVERAGEIFS*	Average of all the values in a range that meet multiple criteria Example: AVERAGEIFS(salaries, employees, “a*”,departments, “Purchasing”) Average salary of employees in Purchasing department whose name starts with letter a.

* these formulas do not work in Excel 2003 or earlier versions.

Comparing a list of values with another list (array compare):

This is where it gets interesting. You have 2 lists of values, like in our last week’s problem. And you want to calculate some value, for eg. Sum of all donations where Amount Donated < Amount Pledged. How do you go about this? Well, this is where we use Array Formulas. In the above case, assuming we have amount donated in lstGiven and amount pledged in lstPledged, We can use the array formula =SUM((lstGiven)*(lstGiven<lstPledged)) to find the sum of all donations such that amount donated is less than amount pledged. Note: You must press CTRL+SHIFT+Enter to get this formula work

How does this formula work?

1. The formula checks for lstGiven < lstPledged and returns a bunch of TRUE, FALSE values.
2. When you multiply this with lstGiven, Excel would convert TRUE, FALSE to 1 and 0 and then multiply.
3. Since 0 multiplied by anything would 0, we end up with a bunch of donation values where donated amount is less than pledged amount.
4. Once all the values are there, the SUM would just add them up.

More examples & Illustration:

Look at below image to understand how we can compare lists of values in Excel using Array formulas.

Solution to Donation Summary Calculation Problem:

I have made a video explaining how you can solve the last week’s homework. See it below or on our Youtube Channel.

Download the Excel Workbook for this.

Click here to download the Workbook with partial solution as shown in the video. Click here to download the solution workbook and play with the formulas to learn more.

Share your tips on Array Formulas

Array formulas are quite powerful and robust. I use them all the time and love to learn more. So please share your tips and ideas using comments. Go! from: http://chandoo.org/wp/2011/06/14/compare-lists-array-formula/

Speed up data-entry tasks by using End Mode

The other day, I was working on a huge worksheet with lots of rows in Excel table columns that I needed to fill out, often with the same text or values. To do this as quickly as possible, I used something called End mode. Many of my colleagues have never heard of End mode, so I thought that you might not know about it either. It's a handy status bar option, and in my opinion it's easier to use than fill down when you have a lot of rows of data to change. Let me walk you through the steps for using End mode. When you right-click the status bar, you'll see the list of all status bar options. A check mark next to End mode indicates that it is available for use. This is the default setting for End mode in Excel. If there's no check mark, simply click End mode to activate it.   To use End mode to quickly select the cells you want to change in a column, it helps to first display only those cells. You can do this by filtering the column. For example, to replace cells that contain data, there shouldn't be any blank cells in the column, and to enter new data in blank cells, there shouldn't be any cells with data in the column. So before you start, you may want to filter for data you want to change or for just the blank cells. Sometimes, I filter on related data in other columns to display the cells I want to fill out with the new data. When you have the right cells displayed, simply select the first cell in the column, and then press the End key once to start End mode. End mode should appear in the status bar to show you that it's turned on. Pressing End turns End mode on and off so it's helpful to see this indicator there! When you press Shift+Down Arrow in End mode, the selection quickly extends to the very last cell in the column. Then you can type the text or value you want in the active cell, but instead of pressing Enter, press Ctrl+Enter (another handy shortcut that my colleague Gary just told me about). The same text or value is immediately entered and displayed in all selected column cells. Done! It's more likely that you want to fill out columns this way, but you can also use End mode to enter the same data in the cells of a row. In that case, you'd press End and then use Shift+Right Arrow to select all cells in that row. Of course, you won't be able to filter for specific data the same way you can in a column, and you may have to temporarily hide columns to show only data you want to change. End mode is also great for finding the last cell in any row or column in a range of worksheet data, the first and last cell in any other non-contiguous ranges, as well as the very last cell in the row or column. Simply click anywhere in your data, press End once to turn on End mode followed by the Right Arrow or Down Arrow to find the last cell that contains data in the row or column. If you repeat pressing End followed by the arrow key, you'll find the first and last cell that contains data in other ranges on the worksheet, and eventually the very last cell in the row or column. Just remember to press End to turn End mode on again because it will automatically turn off after pressing the arrow keys. My colleagues tell me they'll be using End mode more often. I use it all the time, and Gary has gotten me used to using Ctrl+Enter. I love it when we learn from each other! From: http://blogs.office.com/b/microsoft-excel/archive/2011/09/06/speed-up-data-entry-tasks-by-using-end-mode.aspx

Count Words in a Cell, Excel Formula

The formula below will return the number of words (not characters) in cell A1 =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 This basically figure out how many characters there are, counts how many spaces there are, subtracts them and adds 1 This is covered in our Excel Power Users Formula Class