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.

Thanks it works.

I want to add also a random between code that will give me a random amount
between the lowest and highest point of that row. The formula I would use in
excdel would be: LARGE(B6:K6,RANDBETWEEN(1,COUNTIF(B6:K6,"1"))).

Can you help me finish it off? Thanks so much.

Art.

"AltaEgo" wrote:

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.

Application.WorksheetFunction does not have a reference to RandBetween (i.e.
you cannot call it this way). The following piece of code should work.
Place it before the first of the End If lines.


Randomize
mylarge =
Application.WorksheetFunction.Large(Range(myrange) , 1)
mysmall =
Application.WorksheetFunction.Small(Range(myrange) , 1)
myRandBetween = Int(Rnd() * (mylarge - mysmall + 1) +
mysmall)


To set the value in N1 add the following

Target.Offset(, 13) = myRandBetween


--
Steve

"art" wrote in message
...
Thanks it works.

I want to add also a random between code that will give me a random amount
between the lowest and highest point of that row. The formula I would use
in
excdel would be: LARGE(B6:K6,RANDBETWEEN(1,COUNTIF(B6:K6,"1"))).

Can you help me finish it off? Thanks so much.

Art.

"AltaEgo" wrote:

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.

Just occurred to me you may want to specify a number of decimal places or
leave it open. If so,

Obtain ready rolled code RandomNumbers from
http://www.fontstuff.com/vba/vbatut06.htm

and copy it either below your code or into a general module.


Then insert the following before the first End If of the SelectionChange
code:


mylarge = Application.WorksheetFunction.Large(Range(myrange) , 1)
mysmall = Application.WorksheetFunction.Small(Range(myrange) , 1)
myRandBetween = RandomNumbers(mysmall,mylarge, x )
'replace 'x' above with your required decimal places
' delete ', x' if you don't care about decimal places.
' but don't miss deleting the comma
Target.Offset(, 13) = myRandBetween
' will set the value in column N of appropriate row




--
Steve

"AltaEgo" <Somewhere@NotHere wrote in message
...
Application.WorksheetFunction does not have a reference to RandBetween
(i.e. you cannot call it this way). The following piece of code should
work.
Place it before the first of the End If lines.


Randomize
mylarge =
Application.WorksheetFunction.Large(Range(myrange) , 1)
mysmall =
Application.WorksheetFunction.Small(Range(myrange) , 1)
myRandBetween = Int(Rnd() * (mylarge - mysmall + 1) +
mysmall)


To set the value in N1 add the following

Target.Offset(, 13) = myRandBetween


--
Steve

"art" wrote in message
...
Thanks it works.

I want to add also a random between code that will give me a random
amount
between the lowest and highest point of that row. The formula I would use
in
excdel would be: LARGE(B6:K6,RANDBETWEEN(1,COUNTIF(B6:K6,"1"))).

Can you help me finish it off? Thanks so much.

Art.

"AltaEgo" wrote:

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.

I want the random to give me a random number only from the prices that are on
that row, so if I have on that row, 1.19 1,49, 1.89, so the random should
only choose between these three numbers.

Thanks for your help

"AltaEgo" wrote:

Application.WorksheetFunction does not have a reference to RandBetween (i.e.
you cannot call it this way). The following piece of code should work.
Place it before the first of the End If lines.


Randomize
mylarge =
Application.WorksheetFunction.Large(Range(myrange) , 1)
mysmall =
Application.WorksheetFunction.Small(Range(myrange) , 1)
myRandBetween = Int(Rnd() * (mylarge - mysmall + 1) +
mysmall)


To set the value in N1 add the following

Target.Offset(, 13) = myRandBetween


--
Steve

"art" wrote in message
...
Thanks it works.

I want to add also a random between code that will give me a random amount
between the lowest and highest point of that row. The formula I would use
in
excdel would be: LARGE(B6:K6,RANDBETWEEN(1,COUNTIF(B6:K6,"1"))).

Can you help me finish it off? Thanks so much.

Art.

"AltaEgo" wrote:

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.

The code from my other response should fix that.

Don't forget, if you don't need to use Large() and Small(), you can use

mysmall = Application.WorksheetFunction.Min(Range(myrange))
mylarge = Application.WorksheetFunction.Max(Range(myrange))

Also...

On second thoughts, try this. It uses enough of a mix between Large() and
Min() that you should be able to understand how to vary construction.
Note: the use of RandomNumbers() without the optional number of decimals
specified.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const amt1 = 1 ' 1 = largest value, 2 = 2nd largest etc
Dim myRange
Dim mySmall As Long
Dim myLarge As Long
Dim myRandBetween As Long
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
myLarge =
Application.WorksheetFunction.Large(Range(myRange) , amt1)
Target.Offset(, 12) = myLarge
Randomize
mySmall =
Application.WorksheetFunction.Min(Range(myRange))
myRandBetween = RandomNumbers(mySmall, myLarge)
Target.Offset(, 13) = myRandBetween
End If
End If
End If
End Sub
Public Function RandomNumbers(Lowest As Long, Highest As Long, _
Optional Decimals As Integer)
Application.Volatile 'Remove this line to "freeze" the numbers
If IsMissing(Decimals) Or Decimals = 0 Then
Randomize
RandomNumbers = Int((Highest + 1 - Lowest) * Rnd + Lowest)
Else
Randomize
RandomNumbers = Round((Highest - Lowest) * Rnd + Lowest, Decimals)
End If
End Function

Its time for me to cook, relax and sleep. If you need further help, just
ask.


--
Steve

"art" wrote in message
...
I want the random to give me a random number only from the prices that are
on
that row, so if I have on that row, 1.19 1,49, 1.89, so the random should
only choose between these three numbers.

Thanks for your help

"AltaEgo" wrote:

Application.WorksheetFunction does not have a reference to RandBetween
(i.e.
you cannot call it this way). The following piece of code should work.
Place it before the first of the End If lines.


Randomize
mylarge =
Application.WorksheetFunction.Large(Range(myrange) , 1)
mysmall =
Application.WorksheetFunction.Small(Range(myrange) , 1)
myRandBetween = Int(Rnd() * (mylarge - mysmall + 1) +
mysmall)


To set the value in N1 add the following

Target.Offset(, 13) = myRandBetween


--
Steve

"art" wrote in message
...
Thanks it works.

I want to add also a random between code that will give me a random
amount
between the lowest and highest point of that row. The formula I would
use
in
excdel would be: LARGE(B6:K6,RANDBETWEEN(1,COUNTIF(B6:K6,"1"))).

Can you help me finish it off? Thanks so much.

Art.

"AltaEgo" wrote:

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.

Just read the question again. I totally missed the point!

Let me think out loud:
1) count valid values
2) store values in array
3) find random between 1 and the count (call it n)
4) pull nth number from array.

OK

How do you store number in the range. Do they always fill from the left-most
cell?

for example

1.19,1.49,1.89,1.12,blank,blank,blank...

or can they be stored:

blank, 1.19,blank,1.49,1.89,1.12,blank,blank...


--
Steve

"art" wrote in message
...
I want the random to give me a random number only from the prices that are
on
that row, so if I have on that row, 1.19 1,49, 1.89, so the random should
only choose between these three numbers.

Thanks for your help

"AltaEgo" wrote:

Application.WorksheetFunction does not have a reference to RandBetween
(i.e.
you cannot call it this way). The following piece of code should work.
Place it before the first of the End If lines.


Randomize
mylarge =
Application.WorksheetFunction.Large(Range(myrange) , 1)
mysmall =
Application.WorksheetFunction.Small(Range(myrange) , 1)
myRandBetween = Int(Rnd() * (mylarge - mysmall + 1) +
mysmall)


To set the value in N1 add the following

Target.Offset(, 13) = myRandBetween


--
Steve

"art" wrote in message
...
Thanks it works.

I want to add also a random between code that will give me a random
amount
between the lowest and highest point of that row. The formula I would
use
in
excdel would be: LARGE(B6:K6,RANDBETWEEN(1,COUNTIF(B6:K6,"1"))).

Can you help me finish it off? Thanks so much.

Art.

"AltaEgo" wrote:

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.