Check cell contents

Please can someone help. Am very new to building macros. Am creating a report from data imported to excel. I need to go down a colum of text descriptions after they have been sorted. When the code reaches a cell where the description is dfferent I need to insert a blank row into which a heading will be placed. Can someone give me an idea how to tackle this please

Important to know if you will be doing this EVERY time the entry in that column changes or only the first time.

If EVERY time, you are better to work from the bottom up instead of the top down. Otherwise, it is easy to lose track of where you are (i.e., to miss a row each time).

Next tip is that you are always better off thinking in terms of column numbers (that is the way Excel is built, internally).

So, let's assume that you want to work on Column E (5 is my favorite number). Let's assume that you know which row you want to start in (we'll start in row 3), but you need to figure out which is the last row.

Dim ws as Worksheet
Dim myCol as Long, lastRow as Long
Dim i as Long, firstRow as Long
Dim strHeader as String

Set ws = ActiveSheet
myCol = 5
firstRow = 3
'find last Row in myCol
lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

For i = lastRow to firstRow Step -1
If Cells(i, myCol) <> strHeader Then
'we have found a change in myCol
strHeader = Cells(i, myCol).Value
Rows(i).Insert
Cells(i, myCol).Value = "my new header goes here"
End If
Next i

So what you advise is to go to the bottom of the column containing a list of we'll say 100 descriptions made up of say 5 categories of descriptions and work upwards inserting the row which will contain the heading for the category below it?
If this is the case and judging by your answer you are well experienced at dealing with these tasks could you advise me of a source of reading so I could at least get the basics of this type of macro building under my belt first.
I knew this wasnt going to be easy but am willing to stick with it and learn.

I apologize. My explanation was apparently not very clear. Let me try again.

This is not a very complicated macro. But, I can remember writing my first macros and some of the VBA & Excel was not obvious to me. (It is really more about learning Excel than it is about learning VB.)

The first thing to do is to turn on the macro recorder (Tools >> Macro >> Record New Macro).

Select the range you want to sort and sort it the way you normally would.

Find where you would want a new row inserted, and insert the new row manually like you normally would.

Now turn off the macro recorder and look at what you have. Maybe something like this:

Please Login or Register  to view this content.

Out of all of this code, you really only need a few lines:

Please Login or Register  to view this content.

The rest of the task is understanding a little bit about Visual Basic, a little bit about Excel, and a lot about the data you will be handling.

How will you know which row to start in and which row to stop in?
How do you know which column to sort by?
You have explained how you will know where to insert a blank row ... the "rule" will be that when you will insert a blank row every time the entry in a certain column (assume this is the column you want to sort by) changes.
How do you know what new heading will be placed in the new row that you inserted?

My example code assumed that you know which row to start in. But, just in case it changes, I used a variable for that row number (firstRow).

My example code assumed that you know which column to examine. But, just in case it changes, I used a variable for that column number (myCol).

My example assumed that you had already sorted the data. Bad assumption. I will need to fix that.

My example code showed how to tell when the entry in "myCol" column changes ... I used a variable named strHeader; when the entry in a given row (I used i for the variable representing the row number) did not match strHeader, I inserted a new row and redefined strHeader to start the search for the next "break point".

Now, let me start over again.

Visual Basic for Applications (VBA) can be used to fully automate and control almost every feature in Excel. Using VBA is not terribly challenging. What challenge there is comes from the fact that Excel offers a vast array of objects (worksheets, charts, pivot tables, functions, menu bars, buttons, check boxes, et cetera) that can be manipulated using VBA, and an array of Events that can trigger a subroutine to run (clicking a button is an "Event"; other events include opening a Workbook, selecting a Worksheet, changing the value in a cell, and many others). In addition, there are things that are impossible to do manually in Excel; but, which are simple to do in VBA.

If you are going to write (or just read) VBA code, you will use the Visual Basic Editor (VBE). To open the VBE, you can use the Excel menu bar and Select Tools >> Macros >> Visual Basic Editor. I suggest, instead, you use Alt+F11. Why? Because Alt+F11 will Toggle between VBE and Worksheet views of Excel. Whenever I use VBA, I find myself toggling a lot, and I suspect that you will too.

As a general orientation to the Visual Basic Editor (VBE), there are 4 Windows you want to become familiar with:
1. the Project Explorer Window;
2. the Properties Window;
3. the Object Browser Window; and,
4. the Code Window.

You should see the Project Explorer window on the left side of the VBE. If it is not already visible, make it visible in one of 3 ways:
i. use the VBE menu bar and select View >> Project Explorer
ii. use shortcut keys Ctrl+R
iii. use the VBE Standard toolbar and select the icon for Project Explorer

To expand a view, click on the symbol +; to collapse a view, click -.

Note that each Project and each Microsoft Excel Object has 2 names. The first name is called the "CodeName"; the second name (the one in parentheses) is simply called the "Name". The latter is what you are accustomed to seeing in the worksheet view of Excel. By default, the CodeName of each workbook is VBAProject.

Visual Basic Code is always written in a Code Window. There are 3 types of code windows:
i. Object Code Windows
ii. General Code Modules
iii. Class Modules


I mentioned above that Worksheets and Workbooks are Microsoft Excel Objects. These objects have their own Code Windows. (Forms are another type of Object that has its own Code Window.) To view an object's Code Window, double-click on one of the Objects. In many (most) cases Worksheet and Workbook Code Windows are empty.

There is nothing special about Class Modules, per se. Anything that can be coded in a General Module could be coded in a Class Module.

You would need to use a Class Module to define a custom Object and endow it with Properties and Methods that you would define. Discussion of custom Objects is an advanced topic. You can program for years without ever finding a need for a creating a custom Object.

When you look at code created by the Macro Recorder, you will notice that it is color coded. The colors in the code tell us something (but not very much). Text that is blue tells us that these are "reserved" words in Visual Basic. Text that is black is either a variable that we defined ourselves, or an Excel Object, Property, or Method. (See, I told you that the colors would not tell us very much.)

The project that you are going to tackle requires you to examine a number of rows, one at a time. We normally call this a "loop". There are several types of "looping" procedures you can use. The basic ones are:
+ Do ... Loop (while or until)
+ For ... Next (Each or counted)

The Loop in my example is a For ... Next loop.
The statement:

Please Login or Register  to view this content.

tells VBA to assign the number corresponding to the "lastRow" to the variable i. VBA does that, then runs each of the lines of code until it reachs the statement:

Please Login or Register  to view this content.

When it reaches that statement, it "loops" back to the "For" statement and assigns the "next i". Since I told VBA to "Step -1", the "next i" will be 1 less than the lastRow. This loop will continue until:
+ the number for i become less than the number for firstRow
+ an error occurs
+ I tell it to do something else

In this case, I provided no instructions about how to handle errors (but I could have), and I provided no other way for VBA to get out of the loop (but I could have).

See if this helps. If not, write again and tell me what you want to know more about.

Many thanks for all that info, its not that you didnt make things clear its my 2nd attempt at excel macros. Been working in SAP for years, now back in the real world. Only realy worked with macros in Lotus previously. Your assistance is very much appreciated.Will work through it as best I can.
Cheers.
PN