Range with "Large"
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.
|