getting value filtering series of data

[ckb_bd]

Hi all
Can anyone help me!!!
I am explaining the problem, I am trying to write a macro to find the
value A, corresponding to maximum value of D of a range of A values
(say A=6 to 10.5),
A B C D
0 0 0 0
4 0.010792285 150.3484068 2.58938E-06
5 0.009873071 24.47563184 1.24368E-05
6 0.019727386 345.1068965 4.80516E-05
6.5 0.041660798 242.8148119 7.26371E-05
7 0.09908156 193.5461266 0.000116676
7.5 0.124952681 167.7272614 0.000215809
8 0.10273104 135.9831675 0.000300097
8.5 0.081458799 76.67124238 0.000305775
9 0.132468668 18.87816881 0.000246303
9.5 0.227877539 353.0003297 0.000175705
10 0.333064374 339.2699452 0.000178194
10.5 0.43478522 330.3798441 0.000252355
11 0.526932227 323.9273295 0.000324887
11.5 0.607145865 318.885661 0.000370728
12 0.675315927 314.7447969 0.000390683
12.5 0.732447812 311.227155 0.000392778
13 0.779975241 308.1682968 0.000383911
13.5 0.819406661 305.468458 0.000368906
14 0.852094769 303.0589086 0.000350575
14.5 0.879207908 300.8935055 0.000331675
15 0.90175447 298.9374976 0.000312242
15.5 0.920536129 297.161394 0.000294002
16 0.936208927 295.5444679 0.000276399
16.5 0.949317073 294.0664348 0.000260046
17 0.960293536 292.7136982 0.00024447
17.5 0.969508729 291.470526 0.000231317
18 0.977247323 290.3262732 0.000218649

Thank you

 

[Dave Peterson]

You could use a formula:

=MAX((A2:A30>=6)*(A2:A30<=10.5)*D230)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

In code, you can evaluate the same formula:

msgbox activesheet.evaluate("MAX((A2:A30>=6)*(A2:A30<=10.5)*D230)")
or
dim Val1 as double
dim Val2 as double
val1 = 6
val2 = 10.5
msgbox activesheet.evaluate("MAX((A2:A30>=" & Val1 _
& ")*(A2:A30<=" & Val2 & ")*D230)")

Actually, you may want to add some checks to see if there are numbers in the
range you specify (6 to 10.5)

 

[ckb_bd]

You could use a formula:

=MAX((A2:A30>=6)*(A2:A30<=10.5)*D230)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

In code, you can evaluate the same formula:

msgbox activesheet.evaluate("MAX((A2:A30>=6)*(A2:A30<=10.5)*D230)")
or
dim Val1 as double
dim Val2 as double
val1 = 6
val2 = 10.5
msgbox activesheet.evaluate("MAX((A2:A30>=" & Val1 _
& ")*(A2:A30<=" & Val2 & ")*D230)")

Actually, you may want to add some checks to see if there are numbers in the
range you specify (6 to 10.5)








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks dave, but I want the value of A corresponding to Maximum D
value.

Could you please help me again!!!!!!!!

 

[Dave Peterson]

How about just looping through the cells:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim myMaxCell As Range

Dim Val1 As Double
Dim Val2 As Double
Dim myMax As Double

Val1 = 6
Val2 = 10.5
Set myMaxCell = Nothing
myMax = -1E+100 ' a very small number

With Worksheets("sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value >= Val1 _
And myCell.Value <= Val2 Then
If myCell.Offset(0, 3).Value > myMax Then
Set myMaxCell = myCell
myMax = myCell.Offset(0, 3).Value
End If
End If
Next myCell

If myMaxCell Is Nothing Then
MsgBox "no numbers between"
Else
MsgBox myMaxCell.Value & vbLf & myMax
End If

End Sub

 

[ckb_bd]

How about just looping through the cells:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim myMaxCell As Range

Dim Val1 As Double
Dim Val2 As Double
Dim myMax As Double

Val1 = 6
Val2 = 10.5
Set myMaxCell = Nothing
myMax = -1E+100 ' a very small number

With Worksheets("sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value >= Val1 _
And myCell.Value <= Val2 Then
If myCell.Offset(0, 3).Value > myMax Then
Set myMaxCell = myCell
myMax = myCell.Offset(0, 3).Value
End If
End If
Next myCell

If myMaxCell Is Nothing Then
MsgBox "no numbers between"
Else
MsgBox myMaxCell.Value & vbLf & myMax
End If

End Sub









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you very much

Is it possible to write it in formula without writting the code, I
don't know VB that much.

Thanks for your help

 

[Dave Peterson]

Still an array formula:

=INDEX(A2:A30,MATCH(MAX((A2:A30>=6)*(A2:A30<=10.5)*D230),
((A2:A30>=6)*(A2:A30<=10.5)*D230),0))

 

[ckb_bd]

Still an array formula:

=INDEX(A2:A30,MATCH(MAX((A2:A30>=6)*(A2:A30<=10.5)*D230),
((A2:A30>=6)*(A2:A30<=10.5)*D230),0))










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you very much Dave, It is greatly appreciated
Banik