Macro to Check Two numbers and insert a line

[Iain Mac]

I have a few columns of data, comparing some financial data. Both sets of
financial data have identifying detail codes, but both years do not have all
the same codes. So what I want to do is check the code on (say) A2 against
E2, and depending on the result carry out a function
A B C D E F G
-----------------------------------------------------------
1 Detail Desc £ Detail Desc £
2 30116 XYZ 1,000 30116 ABC 1,000
3 30140 QAZ 2,000 31000 QWE 2,000
4 32000 PLM 3,000 31500 OKN 3,000

So in this small example I need to macro to do the following:
a) Check A2 to E2. As A = E, this is ok so the macro can move onto the next
row
b) Check A3 to E3. As A < E, then highlight the range A3:C3 and insert a
row to these 3 cells, moving it down
c) Check A4 to E4. As A > E, then highlight the range E4:G4 and insert a
row to these 3 cells, moving it down

Then carry on until the end of the data

I am learning VBA (Using Excel 97), but and not work this out, can anyone
help please?

 

[Mike H]

Hi,

Try this:-
Sub mersible()
lastrowcola = Range("A65536").End(xlUp).Row
Set myRange = Range("A2:A" & lastrowcola)
For x = lastrowcola To 1 Step -1
Cells(x, 1).Select
If Cells(x, 1).Value <> Cells(x, 1).Offset(0, 4).Value Then
Selection.EntireRow.Insert
Else
End If
Next
End Sub

Mike

 

[Mike H]

Iain,

Set myRange = Range("A2:A" & lastrowcola)

That row does nothing, I was originally doing this another way and forgot to
delete it.

Mike

 

[Iain Mac]

Thanks Mike, appreciate the help

What you have suggested almost covers what I am needing

I need to really do two different things depending on the relationship of A
to E

If A > E, I need to insert a line only in A:C (not the whole row)
If A < E, I need to insert a line only in E:G (Not the whole row)

Anyone have an idea to expand Mikes great macro?