Spy Journal 3.0 Filtering the web for you

I am combining the function of the week with a request I had to write some VBA code to use it. (Note the VBA function is not always the same as the application function).

Here is the question I was asked:

I have two columns - One "AA" and the other "AB"
I want to fill down the column AB based on the results in "AA" but I'm
confused about how to get the individual data from AA2 into AB2 - for
example...
Dim AAcell As Range
Dim ABcell As Range
For Each AAcell In Range("AA:AA")
If IsEmpty(AAcell) Then
    ABcell.Value = ""
Else
    ABcell.Value = Int(AAcell.Value)
End Sub
I'm sure this isnt' right/correct - as it isn't working - LOL - but I'm not
sure how to put it...

Reading this through I understood that the reader wanted to be able to fill a column based on another column. If the first column had a blank cell he wanted that repeated in the new column. All other entries in the new column were to be the INTEGER of the number in the first column. I have provided the code used to make this work at the end of this article. Click Read More to continue.

I have decided to write a short post once a week looking at a single Excel function. This week we are going to look at the IF function.

Definition from Excel Help

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, value_if_true, [value_if_false])

My explanation

The IF function is best thought of as a solution to “either - or” scenarios. Here are some good examples with the syntax to use for each one.

I had an interesting question about conditional formatting posed in the comments by Stephen.

In a new sheet, I am trying to make a whole row turn red, green or amber depending on the value of one cell in that row, so I can easily see which jobs we have won, lost or are pending. Any 'IF' conditional formula I write gets thrown out by Excel. What am I doing wrong?

I promised him an answer so here it is.

For this exercise I am making some assumptions.

• You are using Excel 2007 format Excel spreadsheet (.xlsx or .xlsm). These instructions will not work in detail for Excel 2003, though the concept is similar.
• That there are 3 conditions we  are looking for. Of course Excel 2007 allows more than 3 conditions so you can add more if you need. (One of the improvements on Excel 2003 that only allowed 3 rules)
• That the entire row is needed to be coloured. If you need a smaller section than change the formulas accordingly.
• That the entire worksheet needs this formatting. If you need a smaller section than change the formulas accordingly.
• That the conditional formats are going to be based on a cell that returns a specific result based on some other rule.

I promised to start writing a series of VBA primers. This is the first one.

For many using Excel is a daily thing. From office secretaries who manage the petty cash through to financial whizzkids who monitor stocks and bonds, Excel is a very versatile tool. It also has a lot of potential for automating repetitive tasks easily by being able to create macros.

A macro is a simply a “recorded set of instructions”. These can be as simple as saving two different sets of print options so you don’t have to continually change them. Basically the concept of a Macro is that you can get the computer to record the key and mouse strokes you use and save them so they can be repeated. While the reality is that the macro that is recorded is written in a language called VBA (visual Basic for Applications) this is irrelevant to people who first start using it.