Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query during macro run
Thank to Kevin Knierim for help with the selection criteria on m
backoder report macro. Now that I have all of our reps together, want to have the macro query column B (reps name) and insert a blan row below the name (for all reps), and then sort the sales orders (fo the same reps) by oldest date. And do it for all reps. EXA: Raw Data Column B Column F Rep Name SO Date John Smith 5/21/04 John Smith 5/3/04 John Smith 5/1/04 John Doe 5/21/04 John Doe 5/3/04 John Doe 5/1/04 After macro run: Column B Column F Rep Name SO Date John Smith 5/1/04 John Smith 5/3/04 John Smith 5/21/04 John Doe 5/1/04 John Doe 5/3/04 John Doe 5/21/0 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query during macro run
Try this
Sub SortANdInsert() Dim i As Long Application.ScreenUpdating = False cLastRow = Cells(Rows.Count, "B").End(xlUp).Row Columns("C:C").Insert Shift:=xlToRight Range("C2").FormulaR1C1 = "=MID(RC[-1],FIND("" "",RC[-1])+1,999)" Range("C2").AutoFill Destination:=Range("C2:C" & cLastRow), Type:=xlFillDefault Columns("B:F").Sort Key1:=Range("C2"), _ Order1:=xlAscending, _ Key2:=Range("F2"), _ Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Columns("C:C").Delete Shift:=xlToLeft For i = cLastRow To 2 Step -1 If Cells(i, "B").Value < Cells(i - 1, "B").Value Then Cells(i, "B").EntireRow.Insert ' i = i - 1 End If Next i Application.ScreenUpdating = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "mkingsley " wrote in message ... Thank to Kevin Knierim for help with the selection criteria on my backoder report macro. Now that I have all of our reps together, I want to have the macro query column B (reps name) and insert a blank row below the name (for all reps), and then sort the sales orders (for the same reps) by oldest date. And do it for all reps. EXA: Raw Data Column B Column F Rep Name SO Date John Smith 5/21/04 John Smith 5/3/04 John Smith 5/1/04 John Doe 5/21/04 John Doe 5/3/04 John Doe 5/1/04 After macro run: Column B Column F Rep Name SO Date John Smith 5/1/04 John Smith 5/3/04 John Smith 5/21/04 John Doe 5/1/04 John Doe 5/3/04 John Doe 5/21/04 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query during macro run
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "mkingsley " wrote in message ... Thank to Kevin Knierim for help with the selection criteria on my backoder report macro. Now that I have all of our reps together, I want to have the macro query column B (reps name) and insert a blank row below the name (for all reps), and then sort the sales orders (for the same reps) by oldest date. And do it for all reps. EXA: Raw Data Column B Column F Rep Name SO Date John Smith 5/21/04 John Smith 5/3/04 John Smith 5/1/04 John Doe 5/21/04 John Doe 5/3/04 John Doe 5/1/04 After macro run: Column B Column F Rep Name SO Date John Smith 5/1/04 John Smith 5/3/04 John Smith 5/21/04 John Doe 5/1/04 John Doe 5/3/04 John Doe 5/21/04 --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query during macro run
Code ------------------- Sub SortAndInsertSpaces() Dim iRow As Long Dim strLast As String Columns("A:H").Select 'Change this to your columns Range("A1:H7").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _ "F2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom Range("A1").Select iRow = 2 'Set to your first row of real data strLast = Range("B" & iRow) Do Until Range("B" & iRow).Value = "" If Range("B" & iRow).Value < strLast Then Range("B" & iRow).EntireRow.Insert iRow = iRow + 1 End If strLast = Range("B" & iRow) iRow = iRow + 1 Loop End Su ------------------- Basically, you sort the data first by name then by date, then inser the rows. The row insert code simply counts down column B and inserts a new row when the last value was different than the current one. I tested this on a small set of data, so I thin it will work for you once you make changes to apply to your worksheet (Sort range and star row). -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Query | Excel Discussion (Misc queries) | |||
Macro Query | Excel Discussion (Misc queries) | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
best way to query during macro run | Excel Programming | |||
Web Query in Macro | Excel Programming |