Check if cell <> selection

Hello all

Im currently working on a university project and having some trouble...

background;
Im given a CSV dump of montly data.
As you can see, each set of data has 4 rows of information. The amount changes every month. As you can see in the pic, 4 claims are shown out of a list of 140.
http://img155.imageshack.us/img155/7541/csvdump.jpg

**However, not all the time does the data have 4 rows, sometimes it only has 3 rows or 5 rows.**

I basically want the 4 rows of data to be put into 1 single row for that claim. As shown in the pic below, I only want some of the values.
http://img245.imageshack.us/img245/4166/table.jpg

Old Solution:
I have produced a code which copies/pastes the data but quickly turns into a mess due to some data entries not having exactly 4 rows of data.

The current code I have is;

'Grabs SAP Claim# Cell

Dim a, b As Integer

    Rows("1:1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Let a = Selection.Rows.Count

Cells(1, 46).Select

For b = 1 To a
Sheets("CSVDump").Activate
Cells(b, 46).Select
 If ActiveCell.Value = "SAP Claim#" Then

    ActiveCell.Offset(1, 0).Select
        If ActiveCell.Value = "" Then
        ActiveCell.FormulaR1C1 = "-"
        End If
    Selection.Copy
    Sheets("Table").Activate
        Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    
 End If
    
Next b

End Sub

Basically its a loop which looks at column 46 on the csv dump, searches for the "SAP CLAIM#", switches to "Table" Sheet, goes to bottom, offsets and pastes the value.

This above code is adopted for the other 23 values I want shown in the table. Problem is with so much data, it takes 5-10 minutes for it to copy/paste all the values...

Possible Solution
In order to cut down the duration of the process and make sure the data lines up correctly on the table, Ive been tinkering with only using one macro loop copy/paste and then grab the other data via VLOOKUP.

As each row of information has a ROW-ID (101,201,301,501) and a matching TWC#, the plan is to make the first column combine both numbers to have a unique number to use for vlookup.
http://img15.imageshack.us/img15/2315/twcrowid.jpg

So then in the final table, I would have vlookup code for data similar to this;

=VLOOKUP(A1&101,'CSV Dump'!A:AT,21,FALSE)

Where A1 is the TWC# copied/pasted by the macro loop
101 is the rowID
21 is the column of the data I am after

So the above vlookup code would make sure the data is matching to the correct TWC number

The Problem (Almost there :P)
The problem is with my macro code, it offsets and copies/pastes the TWC#. However, the TWC# is repeated 4 times for each set of data. So when the loop is running, it is copying and pasting the TWC# into 4 rows on the table...

I want to know how can i make it check the copied data is not the same as the previous copied data. If it isnt, then paste the new TWC# on the next row..

Sub CSVCONSOL24()

'Grabs Part TWC Cell

Dim a, b As Integer

    Rows("1:1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Let a = Selection.Rows.Count

Cells(1, 2).Select

For b = 1 To a
Sheets("0309").Activate
Cells(b, 2).Select
 If ActiveCell.Value = "TWC  #" Then
 ActiveCell.Offset(1, 0).Select
        
        If ActiveCell.Value = "" Then
        ActiveCell.FormulaR1C1 = "-"
        End If
        
    Selection.Copy
    Sheets("Table").Activate
    Range("Z1").Select
    Selection.End(xlDown).Select

***************
IF copied data does not equal last value in row
THEN
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
END IF
****************
    
End If
    
Next b

End Sub

My problem is between the *...

Also, if anyone has a better idea of how to organise the data more efficiently in the desired table, please do not hesitate to post.

Sorry for the long post, I prefer to explain the whole story as it seems easier to understand the problem

Thanks all!
- Patrick

I'm not sure if there's a better solution, but I can help you with your existing code.

Rather than using the active cell for everything, you would be better off defining ranges. It makes it much easier to keep track of where you are and means you can 'store' more than one cell at a time. For example:

Sub CSVCONSOL24()

'Grabs Part TWC Cell

Dim a As Integer, b As Integer, Cl As Range

    Rows("1:1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Let a = Selection.Rows.Count

Cells(1, 2).Select

For b = 1 To a
Sheets("0309").Activate
Set Cl = Cells(b, 2)
 If Cl.Value = "TWC  #" Then
 Cl.Offset(1, 0).Select
        
        If ActiveCell.Value = "" Then
        ActiveCell.FormulaR1C1 = "-"
        End If
        
    Sheets("Table").Activate
    Range("Z1").Select
    Selection.End(xlDown).Select

'***************
If ActiveCell.Value <> Cl.Value Then 
     ActiveCell.Offset(1, 0).Value = Cl.Value
End If
'****************
    
End If
    
Next b

End Sub

I have left in most of your ActiveCell references as I'm unsure exactly what some of your tests are for, but as you can see I have defined a range to hold the cell with the value you are comparing.

For what you're doing, either version should work. The way I do it, storing the cell and nto jsut the value, has an advantage if you want to go back to that cell later. For example, I often have loops that look like:

Dim C As Range

Set C = Range("A2")

Do Until C.Value=""
    ...
    [do something here]
    ...
    
    Set C = C.Offset(1,0)
Loop

So in the loop I can go off to wherever I want and always come back to the original cell. However you're using a counter to reference the cells directly, so this isn't relevant for the code you're using, so storing the string works just as well in this case.

You can use a very similar loop to the one you're already using:

...
a = Selection.Rows.Count

For b = 1 To a
    Cells(b, 1).Select
    ActiveCell.FormulaR1C1 = ...[your formula]
Next b