Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
I have a table: A1:C11 as follows (Cells C2:C11 are Blank), but Macro
should fill in answer (given to the right): Name Amt Order Answer to Order (=Rank ascending by Name) Jon 13 ? 1 Jon 23 ? 3 Jon 15 ? 2 Peter 27 ? 2 Peter 10 ? 1 Alex 23 ? 2 Alex 34 ? 3 Alex 18 ? 1 Alex 36 ? 4 Mary 15 ? 1 The VBA routine should fill in C2:C11 with the above #'s Can someone help me along? TIA, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
Jim,
Determine the range Find or insert a blank column adjacent to the range. AutoFill the blank column with sequential numbers. Sort the range (by Name then Amt). Include the sequential number column. Loop thru the names, incrementing a counter in Column C. When the name changes, start the counter over again. Re-sort the range using the column with the autofilled numbers as the index. Regards, Jim Cone San Francisco, USA "Jim May" wrote in message news:HqRTd.18310$7z6.11048@lakeread04... I have a table: A1:C11 as follows (Cells C2:C11 are Blank), but Macro should fill in answer (given to the right): Name Amt Order Answer to Order (=Rank ascending by Name) Jon 13 ? 1 Jon 23 ? 3 Jon 15 ? 2 Peter 27 ? 2 Peter 10 ? 1 Alex 23 ? 2 Alex 34 ? 3 Alex 18 ? 1 Alex 36 ? 4 Mary 15 ? 1 The VBA routine should fill in C2:C11 with the above #'s Can someone help me along? TIA, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
Thanks Jim;
I'll give it a go -- in the AM.. Jim "Jim Cone" wrote in message ... Jim, Determine the range Find or insert a blank column adjacent to the range. AutoFill the blank column with sequential numbers. Sort the range (by Name then Amt). Include the sequential number column. Loop thru the names, incrementing a counter in Column C. When the name changes, start the counter over again. Re-sort the range using the column with the autofilled numbers as the index. Regards, Jim Cone San Francisco, USA "Jim May" wrote in message news:HqRTd.18310$7z6.11048@lakeread04... I have a table: A1:C11 as follows (Cells C2:C11 are Blank), but Macro should fill in answer (given to the right): Name Amt Order Answer to Order (=Rank ascending by Name) Jon 13 ? 1 Jon 23 ? 3 Jon 15 ? 2 Peter 27 ? 2 Peter 10 ? 1 Alex 23 ? 2 Alex 34 ? 3 Alex 18 ? 1 Alex 36 ? 4 Mary 15 ? 1 The VBA routine should fill in C2:C11 with the above #'s Can someone help me along? TIA, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
Jim:
I got it !!! << This is my first (to me,,) real programming exercise. <<vbg I'm really excited. I'd never got it without your breaking the project down into it's pieces - which I can't seem to do (my biggest problem, I think..) Again thanks, Here is my go at it (probably could be improved on) - Do you see any obvious improvement(s) that could or should be made? Jim May Sub Foo() Range("D1").Value = "AutoNo" ActiveSheet.UsedRange.Offset(0, 3).Resize(, 1).Select Mrows = Selection.Rows.Count For i = 2 To Mrows Cells(i, 4).Value = i Next i ActiveSheet.UsedRange.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Key2:=Range("B1"), _ Order2:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Range("C2:C" & Mrows).Select p = 0 For n = 2 To Mrows If Cells(n, 1).Value = Cells(n + 1, 1) Then p = p + 1 Cells(n, 3).Value = p Else Cells(n, 3).Value = p + 1 p = 0 End If Next n Range("A1").Select ActiveSheet.UsedRange.Sort Key1:=Range("D1"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Columns("D:D").Delete Range("A1").Select End Sub "Jim Cone" wrote in message ... Jim, Determine the range Find or insert a blank column adjacent to the range. AutoFill the blank column with sequential numbers. Sort the range (by Name then Amt). Include the sequential number column. Loop thru the names, incrementing a counter in Column C. When the name changes, start the counter over again. Re-sort the range using the column with the autofilled numbers as the index. Regards, Jim Cone San Francisco, USA "Jim May" wrote in message news:HqRTd.18310$7z6.11048@lakeread04... I have a table: A1:C11 as follows (Cells C2:C11 are Blank), but Macro should fill in answer (given to the right): Name Amt Order Answer to Order (=Rank ascending by Name) Jon 13 ? 1 Jon 23 ? 3 Jon 15 ? 2 Peter 27 ? 2 Peter 10 ? 1 Alex 23 ? 2 Alex 34 ? 3 Alex 18 ? 1 Alex 36 ? 4 Mary 15 ? 1 The VBA routine should fill in C2:C11 with the above #'s Can someone help me along? TIA, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
Jim,
Only two things... The variables have not been declared and most of the "Select" statements are not needed. I particularly like the way you incremented the Column C values. For what it's worth, do not assume a "UsedRange" will always start in row one. Of course, in this case it does. 'Slightly revised code follows... '------------------------------------ Sub Foo() Dim Mrows As Long Dim i As Long Dim n As Long Dim p As Long Range("D1").Value = "AutoNo" ' ActiveSheet.UsedRange.Offset(0, 3).Resize(, 1).Select ' Mrows = Selection.Rows.Count Mrows = ActiveSheet.UsedRange.Rows.Count '*** Added For i = 2 To Mrows Cells(i, 4).Value = i Next i ActiveSheet.UsedRange.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Key2:=Range("B1"), _ Order2:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom ' Range("C2:C" & Mrows).Select p = 0 For n = 2 To Mrows If Cells(n, 1).Value = Cells(n + 1, 1) Then p = p + 1 Cells(n, 3).Value = p Else Cells(n, 3).Value = p + 1 p = 0 End If Next n ' Range("A1").Select ActiveSheet.UsedRange.Sort Key1:=Range("D1"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Columns("D:D").Delete Range("A1").Select End Sub '--------------------------------------- Regards, Jim Cone San Francisco, USA "Jim May" wrote in message news:DX_Td.23233$7z6.5990@lakeread04... Jim: I got it !!! << This is my first (to me,,) real programming exercise. <<vbg I'm really excited. I'd never got it without your breaking the project down into it's pieces - which I can't seem to do (my biggest problem, I think..) Again thanks, Here is my go at it (probably could be improved on) - Do you see any obvious improvement(s) that could or should be made? Jim May -snip- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
Thanks Jim for the additional comments;
appreciate your help on this. PS: I later came across a formula approach (as follows): In Cell C2 (and copy down).. {=COUNT(IF(($A$2:$A$11=A2)*($B$2:$B$11<=B2),$B$2:$ B$11))} enter as CSE type. Jim May "Jim Cone" wrote in message ... Jim, Only two things... The variables have not been declared and most of the "Select" statements are not needed. I particularly like the way you incremented the Column C values. For what it's worth, do not assume a "UsedRange" will always start in row one. Of course, in this case it does. 'Slightly revised code follows... '------------------------------------ Sub Foo() Dim Mrows As Long Dim i As Long Dim n As Long Dim p As Long Range("D1").Value = "AutoNo" ' ActiveSheet.UsedRange.Offset(0, 3).Resize(, 1).Select ' Mrows = Selection.Rows.Count Mrows = ActiveSheet.UsedRange.Rows.Count '*** Added For i = 2 To Mrows Cells(i, 4).Value = i Next i ActiveSheet.UsedRange.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Key2:=Range("B1"), _ Order2:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom ' Range("C2:C" & Mrows).Select p = 0 For n = 2 To Mrows If Cells(n, 1).Value = Cells(n + 1, 1) Then p = p + 1 Cells(n, 3).Value = p Else Cells(n, 3).Value = p + 1 p = 0 End If Next n ' Range("A1").Select ActiveSheet.UsedRange.Sort Key1:=Range("D1"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Columns("D:D").Delete Range("A1").Select End Sub '--------------------------------------- Regards, Jim Cone San Francisco, USA "Jim May" wrote in message news:DX_Td.23233$7z6.5990@lakeread04... Jim: I got it !!! << This is my first (to me,,) real programming exercise. <<vbg I'm really excited. I'd never got it without your breaking the project down into it's pieces - which I can't seem to do (my biggest problem, I think..) Again thanks, Here is my go at it (probably could be improved on) - Do you see any obvious improvement(s) that could or should be made? Jim May -snip- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solve Using VBA Routine
Jim,
You are welcome and thanks for passing the formula along. You may find, like me, that once you start programming, it seems to be easier to write code rather than figure out formulas. For what it's worth, replace the second occurrence of "Jon" with "Alex", and see what you get using the array formula. Regards, Jim Cone "Jim May" wrote in message news:fK5Ud.23258$7z6.18254@lakeread04... Thanks Jim for the additional comments; appreciate your help on this. PS: I later came across a formula approach (as follows): In Cell C2 (and copy down).. {=COUNT(IF(($A$2:$A$11=A2)*($B$2:$B$11<=B2),$B$2:$ B$11))} enter as CSE type. Jim May "Jim Cone" wrote in message ... Jim, Only two things... The variables have not been declared and most of the "Select" statements are not needed. I particularly like the way you incremented the Column C values. For what it's worth, do not assume a "UsedRange" will always start in row one. Of course, in this case it does. 'Slightly revised code follows... '------------------------------------ Sub Foo() Dim Mrows As Long Dim i As Long Dim n As Long Dim p As Long Range("D1").Value = "AutoNo" ' ActiveSheet.UsedRange.Offset(0, 3).Resize(, 1).Select ' Mrows = Selection.Rows.Count Mrows = ActiveSheet.UsedRange.Rows.Count '*** Added For i = 2 To Mrows Cells(i, 4).Value = i Next i ActiveSheet.UsedRange.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Key2:=Range("B1"), _ Order2:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom ' Range("C2:C" & Mrows).Select p = 0 For n = 2 To Mrows If Cells(n, 1).Value = Cells(n + 1, 1) Then p = p + 1 Cells(n, 3).Value = p Else Cells(n, 3).Value = p + 1 p = 0 End If Next n ' Range("A1").Select ActiveSheet.UsedRange.Sort Key1:=Range("D1"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Columns("D:D").Delete Range("A1").Select End Sub '--------------------------------------- Regards, Jim Cone San Francisco, USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sub routine | Excel Discussion (Misc queries) | |||
Using (what goes in here) for sub routine | Excel Programming | |||
SUB ROUTINE | Excel Programming | |||
Routine?? | Excel Programming | |||
Need VBA Routine | Excel Programming |