Sorry about the delay - my daughter took over ownership of the PC!

Using Dave's (superior) Resize() to change the range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const amt1 = 1 ' 1 = largest value, 2 = 2nd largest etc
Dim myrange
If Target.Count = 1 Then 'one cell selected
Set isect = Application.Intersect(Range("A:A"), Target)
If Not isect Is Nothing Then ' you clicked a cell in column A

myrange = Target.Offset(, 1).Resize(1, 11).Address

If Application.WorksheetFunction.CountA(Range(myrange )) 0 Then
Debug.Print
Application.WorksheetFunction.Large(Range(myrange) , amt1)
Target.Offset(, 12) =
Application.WorksheetFunction.Large(Range(myrange) , amt1)
End If
End If
End If
End Sub



--
Steve

"art" wrote in message
...
Here is what I want to do.

I have lets say in A1 through A10 codes (112, 113, 114...) In B1 through
K10
is prices , so for EG, code 112 could have in B1 1.99 and in C1 2.99 and
D1
3.99 and so on. And they same for each code.

Here is what I want to do. When I select any "code", in cell M1 should
state
the "large" number (or whatever I will select, second to largest, third to
largest and so on). So if I select A2, it should state in M1 the highest
price for that code from that row.

I hope I came across clear.

Thanks.

"AltaEgo" wrote:

Cells is returning values. Large wants a range (more precisely, an
array).

I am slightly confused about your ambition. However, the following MAY
help:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

myrange = Target.Address & ":" & Target.Offset(10).Address

cdamount = WorksheetFunction.Large(Range(myrange), 1)

MsgBox cdamount
End Sub

I interpretted from your question's code that you wanted Large to apply
to
column B.
If you want large applied to a row, instead of a column, just change
Target.Offset(10).Address
to
Target.Offset(,10).Address

If I totally misunderstand what you are trying to do, please add further
information

HTH

--
Steve

"art" wrote in message
...
Hello all:

Can someone please help me with this. I have the following vba code:

cdamount =
WorksheetFunction.Large(Cells(target1,"B"):(Rows(t arget2,"B")),
amount1)
target1 = Target.Row
target2 = Target.Row + 10

The range for "large" does not work correctly, what should I change to
get
this right?

I want to use a worksheet code "Worksheet_SelectionChange" that when I
change my selection it should show me ina differnt cell the "large" of
that
specific row which is active.

Please help.

Thanks.