How to Loop Through This Range and...

[Faye]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Faye]

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