Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to add blank rows based on 2 fields
Field A list ID numbers Field B, C, D, and E, are added from different location Field E is also ID numbers There are more records for Field A than E so I need rows added to fields B,C,D, and E until they match up with Field A. A is assending sort and E is assending sort prior to paste. There will always be an entry in A for each and every entry in E. So B,C,D and E can always be moved down until there is a match between E and A. The code I was provided is: -------------------------------------------------------------------------------- Sub aaa() Range("e2").Select While Not IsEmpty(ActiveCell) If ActiveCell ActiveCell.Offset(0, -4) Then Range("b" & ActiveCell.Row & ":e" & ActiveCell.Row).Insert shift:=xlDown End If ActiveCell.Offset(1, 0).Select Wend End Sub -------------------------------------------------------------------------------- The problem is in my cell format. The ID#'s aren't actually numbers. (alpha-numeric w/preceding 0's) I did try the code with just numbers and it worked great. However, is there a way to acomplish this with a text field? -- voodoo223 ------------------------------------------------------------------------ voodoo223's Profile: http://www.excelforum.com/member.php...o&userid=28799 View this thread: http://www.excelforum.com/showthread...hreadid=484863 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to add blank rows based on 2 fields
Hi,
There is no obvious reason this won't work with text fields. I tested it with apha-numeric fields and correct rows were inserted. "voodoo223" wrote: Field A list ID numbers Field B, C, D, and E, are added from different location Field E is also ID numbers There are more records for Field A than E so I need rows added to fields B,C,D, and E until they match up with Field A. A is assending sort and E is assending sort prior to paste. There will always be an entry in A for each and every entry in E. So B,C,D and E can always be moved down until there is a match between E and A. The code I was provided is: -------------------------------------------------------------------------------- Sub aaa() Range("e2").Select While Not IsEmpty(ActiveCell) If ActiveCell ActiveCell.Offset(0, -4) Then Range("b" & ActiveCell.Row & ":e" & ActiveCell.Row).Insert shift:=xlDown End If ActiveCell.Offset(1, 0).Select Wend End Sub -------------------------------------------------------------------------------- The problem is in my cell format. The ID#'s aren't actually numbers. (alpha-numeric w/preceding 0's) I did try the code with just numbers and it worked great. However, is there a way to acomplish this with a text field? -- voodoo223 ------------------------------------------------------------------------ voodoo223's Profile: http://www.excelforum.com/member.php...o&userid=28799 View this thread: http://www.excelforum.com/showthread...hreadid=484863 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter for blank fields in multiple rows | Excel Discussion (Misc queries) | |||
When I sort a list, How can I keep blank rows between the fields? | Excel Discussion (Misc queries) | |||
When I sort a list, How can I keep blank rows between the fields? | Excel Discussion (Misc queries) | |||
When I sort a list, How can I keep blank rows between the fields? | Excel Discussion (Misc queries) | |||
If Statement based on blank fields | Excel Worksheet Functions |