Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move cell contents with macro | Excel Discussion (Misc queries) | |||
how to move contents in column up one cell | Excel Worksheet Functions | |||
move cell contents | Excel Discussion (Misc queries) | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
How to move cell contents | Excel Discussion (Misc queries) |