If Cell Contents Don't Match, Move All Data Down One Row
 

Hello,
I am a newbie to VBA and am trying to write a simple script which will
compare to cells (I1 & J1 ro example) and if they do not match then I need to
grab the contents of the sheet (starting at that row) and move cell ranges A
thru I down one row. If they match, then I just need to move my cursor down
to the next cell (I2) and start the copmparison again against J2. Essentially
I am trying to line up all contents with its respective row. I am sure
someone has had to do this before and maybe someone could help me get started
or point me in the right direction

If Cell Contents Don't Match, Move All Data Down One Row
 

Sub FFF()
Dim rng As Range, cell As Range
Set rng = Range(Range("J1"), Range("J1").End(xlDown))
For Each cell In rng
If cell.Offset(0, -1) < cell.Value And Not _
IsEmpty(cell.Offset(0, -1)) Then
Cells(cell.Row, 1).Resize(1, 9).Insert Shift:=xlShiftDown
End If
Next
End Sub


Might be what you want.

--
Regards,
Tom Ogilvy




"DJS" wrote in message
...
Hello,
I am a newbie to VBA and am trying to write a simple script which will
compare to cells (I1 & J1 ro example) and if they do not match then I need
to
grab the contents of the sheet (starting at that row) and move cell ranges
A
thru I down one row. If they match, then I just need to move my cursor
down
to the next cell (I2) and start the copmparison again against J2.
Essentially
I am trying to line up all contents with its respective row. I am sure
someone has had to do this before and maybe someone could help me get
started
or point me in the right direction

If Cell Contents Don't Match, Move All Data Down One Row
 

Tom~
Thanks for the help, but this just forces displaces all rows to the bottom
of my sheet (no matches occurred).
Here is an example of my data:

[A] [b] [C] [D] [E] [F] [G] [H] [i] [J]
1 1 0 0 0 17338 17338
2 2 0 0 0 17341 17339
1 1 0 0 0 17343 17340
2 2 0 0 1 17344 17341
0 3 0 0 1 17354 17342
5 5 0 0 2 17543 17343

You will notice that row 2 (val: 17341) needs to be moved down two row so
that it matches up with column J. Also , all rows below it need to be moved
down and then I check the next cell (which should be [I5] if all the rows
moved down 17341 should now be in [I4] and I need to check the cell below it.



"Tom Ogilvy" wrote:

Sub FFF()
Dim rng As Range, cell As Range
Set rng = Range(Range("J1"), Range("J1").End(xlDown))
For Each cell In rng
If cell.Offset(0, -1) < cell.Value And Not _
IsEmpty(cell.Offset(0, -1)) Then
Cells(cell.Row, 1).Resize(1, 9).Insert Shift:=xlShiftDown
End If
Next
End Sub


Might be what you want.

--
Regards,
Tom Ogilvy




"DJS" wrote in message
...
Hello,
I am a newbie to VBA and am trying to write a simple script which will
compare to cells (I1 & J1 ro example) and if they do not match then I need
to
grab the contents of the sheet (starting at that row) and move cell ranges
A
thru I down one row. If they match, then I just need to move my cursor
down
to the next cell (I2) and start the copmparison again against J2.
Essentially
I am trying to line up all contents with its respective row. I am sure
someone has had to do this before and maybe someone could help me get
started
or point me in the right direction

If Cell Contents Don't Match, Move All Data Down One Row
 

Pasted you data in a worksheet and ran the macro.

Produced the expected results.


1 1 0 0 0 17338 17338
17339
17340
2 2 0 0 0 17341 17341
17342
1 1 0 0 0 17343 17343
2 2 0 0 1 17344
0 3 0 0 1 17354
5 5 0 0 2 17543

the two rightmost columns were placed in I and J per your previous
description. Impossible to tell from your post where they actually are.

--
Regards,
Tom Ogilvy



"DJS" wrote in message
...[i]
Tom~
Thanks for the help, but this just forces displaces all rows to the bottom
of my sheet (no matches occurred).
Here is an example of my data:

[A] [b] [C] [D] [E] [F] [G] [H] [J]
1 1 0 0 0 17338 17338
2 2 0 0 0 17341 17339
1 1 0 0 0 17343 17340
2 2 0 0 1 17344 17341
0 3 0 0 1 17354 17342
5 5 0 0 2 17543 17343

You will notice that row 2 (val: 17341) needs to be moved down two row so
that it matches up with column J. Also , all rows below it need to be
moved
down and then I check the next cell (which should be [I5] if all the rows
moved down 17341 should now be in [I4] and I need to check the cell below
it.



"Tom Ogilvy" wrote:

Sub FFF()
Dim rng As Range, cell As Range
Set rng = Range(Range("J1"), Range("J1").End(xlDown))
For Each cell In rng
If cell.Offset(0, -1) < cell.Value And Not _
IsEmpty(cell.Offset(0, -1)) Then
Cells(cell.Row, 1).Resize(1, 9).Insert Shift:=xlShiftDown
End If
Next
End Sub


Might be what you want.

--
Regards,
Tom Ogilvy




"DJS" wrote in message
...
Hello,
I am a newbie to VBA and am trying to write a simple script which will
compare to cells (I1 & J1 ro example) and if they do not match then I
need
to
grab the contents of the sheet (starting at that row) and move cell
ranges
A
thru I down one row. If they match, then I just need to move my cursor
down
to the next cell (I2) and start the copmparison again against J2.
Essentially
I am trying to line up all contents with its respective row. I am sure
someone has had to do this before and maybe someone could help me get
started
or point me in the right direction

If Cell Contents Don't Match, Move All Data Down One Row
 

Hey Tom,
Thanks a million, it does work.
The problem was my data in column J wasn't a numeric value, so no match
would occur.
I don't want to have to modify the value type in either column.
Is there a way to modify the script so that the val in column J could be
interpreted as a numeric val?
Something like: Number(cell.Value)

Thanks Again

"Tom Ogilvy" wrote:

Pasted you data in a worksheet and ran the macro.

Produced the expected results.


1 1 0 0 0 17338 17338
17339
17340
2 2 0 0 0 17341 17341
17342
1 1 0 0 0 17343 17343
2 2 0 0 1 17344
0 3 0 0 1 17354
5 5 0 0 2 17543

the two rightmost columns were placed in I and J per your previous
description. Impossible to tell from your post where they actually are.

--
Regards,
Tom Ogilvy