Can someone tell me what's wrong with this code please?
 

Hi

I have 2 sets of information side by side as I am running a comparrison.

Information 1 is in columns A to D and info 2 is in columns F to I

Info 1 has 867 records with column 2 having slightly more. When I run the code below, everything works fine until it reaches record 867 and then everything below is ignored. How can I ammend the code to keep checking the range beyond it's start point.

Here is the code.

Sub Macro1()
Dim myCell As Range
Dim row As Long

Range("A:D").Sort Key1:=Range("A1"), order1:=xlAscending, header:=xlNo
Range("F:I").Sort Key1:=Range("F1"), order1:=xlAscending, header:=xlNo

For row = 1 To Application.Count(Range("A:A"))

If Cells(row, 6).Value < Cells(row, 1).Value And Cells(row, 1).Value < Cells(row, 6).Value Then
Cells(row, 6).Insert Shift:=xlDown
Cells(row, 7).Insert Shift:=xlDown
Cells(row, 8).Insert Shift:=xlDown
Cells(row, 9).Insert Shift:=xlDown
Else
If Cells(row, 1).Value < Cells(row, 6).Value And Cells(row, 6).Value < Cells(row, 1).Value Then
Cells(row, 1).Insert Shift:=xlDown
Cells(row, 2).Insert Shift:=xlDown
Cells(row, 3).Insert Shift:=xlDown
Cells(row, 4).Insert Shift:=xlDown
End If
End If
Next row
End Sub

Any help with this is greatly appreciated.

Regards

Maclolm Davidson

Can someone tell me what's wrong with this code please?
 

Your code will only take it to the end of column A - you will need to use the column with the most entries too.

Can someone tell me what's wrong with this code please?
 

Work backwards!

For row = Application.Count(Range("A:A")) to 1 step -

HTH

Can someone tell me what's wrong with this code please?
 

Sub Macro1()
Dim myCell As Range
Dim row As Long, i as Long

Range("A:D").Sort Key1:=Range("A1"), order1:=xlAscending, header:=xlNo
Range("F:I").Sort Key1:=Range("F1"), order1:=xlAscending, header:=xlNo

row= 1
Do until isempty(cells(row,"A")) and isempty(Cells(row,"F"))

If Cells(row, 6).Value < Cells(row, 1).Value And Cells(row, 1).Value <
Cells(row, 6).Value Then
Cells(row, 6).Insert Shift:=xlDown
Cells(row, 7).Insert Shift:=xlDown
Cells(row, 8).Insert Shift:=xlDown
Cells(row, 9).Insert Shift:=xlDown
Else
If Cells(row, 1).Value < Cells(row, 6).Value And Cells(row, 6).Value <
Cells(row, 1).Value Then
Cells(row, 1).Insert Shift:=xlDown
Cells(row, 2).Insert Shift:=xlDown
Cells(row, 3).Insert Shift:=xlDown
Cells(row, 4).Insert Shift:=xlDown
End If
End If
row = row + 1
Loop
End Sub

--
Regards,
Tom Ogilvy

"malycom" wrote in message
...
Hi

I have 2 sets of information side by side as I am running a comparrison.

Information 1 is in columns A to D and info 2 is in columns F to I

Info 1 has 867 records with column 2 having slightly more. When I run the
code below, everything works fine until it reaches record 867 and then
everything below is ignored. How can I ammend the code to keep checking the
range beyond it's start point.

Here is the code.

Sub Macro1()
Dim myCell As Range
Dim row As Long

Range("A:D").Sort Key1:=Range("A1"), order1:=xlAscending, header:=xlNo
Range("F:I").Sort Key1:=Range("F1"), order1:=xlAscending, header:=xlNo

For row = 1 To Application.Count(Range("A:A"))

If Cells(row, 6).Value < Cells(row, 1).Value And Cells(row, 1).Value <
Cells(row, 6).Value Then
Cells(row, 6).Insert Shift:=xlDown
Cells(row, 7).Insert Shift:=xlDown
Cells(row, 8).Insert Shift:=xlDown
Cells(row, 9).Insert Shift:=xlDown
Else
If Cells(row, 1).Value < Cells(row, 6).Value And Cells(row, 6).Value <
Cells(row, 1).Value Then
Cells(row, 1).Insert Shift:=xlDown
Cells(row, 2).Insert Shift:=xlDown
Cells(row, 3).Insert Shift:=xlDown
Cells(row, 4).Insert Shift:=xlDown
End If
End If
Next row
End Sub

Any help with this is greatly appreciated.

Regards

Maclolm Davidson

Can someone tell me what's wrong with this code please?
 

Thanks Lawlera & Tom Ogilv

The code is working fine now

Much appreciated.