Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Loop Through This Range and...
My data looks like this:
A B C Faye 14,874 Faye 18,050 Faye 31,255 Faye 56,351 Faye 59,352 Faye 74,887 Jean 42,671 Jean 45,560 Jean 60,170 Jean 100,357 Jean 112,163 Jeff 18,078 Jeff 26,859 Jeff 49,090 Jeff 74,579 .... .... I want to know how to loop through this dataset and for each value in Column A, I will do some calculations for the associated data in Column B and then place the result on Column C. For example, I want to find the minimum/maximum of the value in Column B for the associated value in Column A, then place the result in Column C. I have started the code like this, For RowNdx = Selection(Selection.Cells.Count).Row To Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then ... End If Next RowNdx I need help. Thanks. Faye Larson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Loop Through This Range and...
No need for VBA
enter this in C1 and copy down =IF(OR(B1=MAX(IF($A$1:$A$15=A1,$B$1:$B$15)),B1=MIN (IF($A$1:$A$15=A1,$B$1:$B$ 15))),B1,"") which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Faye" wrote in message oups.com... My data looks like this: A B C Faye 14,874 Faye 18,050 Faye 31,255 Faye 56,351 Faye 59,352 Faye 74,887 Jean 42,671 Jean 45,560 Jean 60,170 Jean 100,357 Jean 112,163 Jeff 18,078 Jeff 26,859 Jeff 49,090 Jeff 74,579 ... ... I want to know how to loop through this dataset and for each value in Column A, I will do some calculations for the associated data in Column B and then place the result on Column C. For example, I want to find the minimum/maximum of the value in Column B for the associated value in Column A, then place the result in Column C. I have started the code like this, For RowNdx = Selection(Selection.Cells.Count).Row To Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then ... End If Next RowNdx I need help. Thanks. Faye Larson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Loop Through This Range and...
Sub CalcExtremes()
Dim rng as Range, i as Long Dim iMax as Long, iMin as Long Dim s as String s = cells(1,1) lMin = cells(1,2) lMax = cells(1,2) set rng = cells(1,1) i = 2 do while cells(i-1,1) < "" if cells(i,1) < s then rng.offset(0,2).Value = lMin rng.offset(0,3).value = lMax set rng = cells(i,1) lMin = cells(i,2) lMax = cells(i,2) s = cells(i,1) end if if lMin cells(i,2) then lMin = cells(i,2) if lMax < cells(i,2) then lMax = cells(i,2) i = i + 1 Loop end Sub -- Regards, Tom Ogilvy "Faye" wrote: My data looks like this: A B C Faye 14,874 Faye 18,050 Faye 31,255 Faye 56,351 Faye 59,352 Faye 74,887 Jean 42,671 Jean 45,560 Jean 60,170 Jean 100,357 Jean 112,163 Jeff 18,078 Jeff 26,859 Jeff 49,090 Jeff 74,579 .... .... I want to know how to loop through this dataset and for each value in Column A, I will do some calculations for the associated data in Column B and then place the result on Column C. For example, I want to find the minimum/maximum of the value in Column B for the associated value in Column A, then place the result in Column C. I have started the code like this, For RowNdx = Selection(Selection.Cells.Count).Row To Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then ... End If Next RowNdx I need help. Thanks. Faye Larson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Loop Through This Range and...
Thanks to both Bob and Tom. They do exactly what I want to do. The next
question I have is, instead of finding minimum/maximum, I would like to create a chart of value of Column B for each group in Column A. I guess this will be accomplished by the VB script? Thanks again. Faye Larson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Loop | Excel Programming | |||
Add a Range to an existing Range in a loop? | Excel Programming | |||
loop in range | Excel Programming | |||
Loop in a Range | Excel Programming | |||
Loop through a range | Excel Programming |