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.

cdamount _
= Application.Large(ActiveSheet.Cells(target1, "B").Resize(11, 1), amount1)

or

with activesheet 'whatever???
cdamount _
= Application.Large(.range(.Cells(target1, "B"), .cells(target2,"B")), _
amount1)
end with


art wrote:

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.

--

Dave Peterson

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.

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.

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.

Thanks, but something is wrong. Which part of this is on one line and which
on the next?

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


"AltaEgo" wrote:

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.

I left a (harmless) debug line in. Now removed from the following:


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
Set isect = Application.Intersect(Range("A:A"), Target)
If Not isect Is Nothing Then

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

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

Now to fix the line wrapping problem:

Where the line ends with '= _' in the above, delete the _ and keep pressing
[Delete] until there is only a single space between '=' and 'Application'.

If that doesn't work, My method is to navigate the cursor to the end of the
first line of code that shows red in my module and press [Delete] until the
code wraps the following line into position. Once it does that, I move to
the end of the joined line and press [Enter]. Keep repeating this process
until all code is black (or green if a comment).



--
Steve

"art" wrote in message
...
Thanks, but something is wrong. Which part of this is on one line and
which
on the next?

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


"AltaEgo" wrote:

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.