Function....

[Christopher lee]

1)Cells((r + rowTO), MedianUETFinterceptTo) = Application.WorksheetFunction.Median(Round(Cells(r + rowTO, uETFinterceptTO)))

2)Cells((r + rowTO), MedianUETFinterceptTo) = Application.WorksheetFunction.Median(Cells(r + rowTO, uETFinterceptTO))

Any 1 can tell me why the 1st coding can be run but the 2nd coding gv me the "Unable to get the Median property of the WorksheetFunction class'' run time error 1004 ??


The 1st coding can be run but it gv me with the 0 result.....any 1 can tell me what the difference with using ''Round'' and without using ''Round'' ??

 

[Teeroy]

The median of a data set is a single number. What do you mean you want it in column BK?

 

[Christopher lee]

ya the median should be a single number n i want put the single in all column bk....means every row in column BK is the single number which is median of the AY......

 

[Christopher lee]

now i use the coding below :

Set Rng1 = Range(Range("AY1"), Range("AY" & Rows.count).End(xlUp).Offset(1))
Set Rng2 = Range(Range("AZ1"), Range("AZ" & Rows.count).End(xlUp).Offset(1))

''uETFintercept''
Cells((r + rowTO), IuTo) = Application.WorksheetFunction.Median(Rng1)
''vETFintercept''
Cells((r + rowTO), IvTo) = Application.WorksheetFunction.Median(Rng2)


The Iu can be run but the Iv gv me the error with unable to get the median property of the worksheetfunction class error.....both Iu and Iv i write in the same method at all....but y Iu can run then Iv gv me the error??

 

[Teeroy]

Try the following as stand alone code and see if it works for you. If it does then you can work out how to incorporate it into your existing code.

Sub test()
Dim lLastRow As Long
Dim Rng1 As Range, rng2 As Range
With Sheets("TFimport")
    With .UsedRange
        lLastRow = .Rows(.Rows.Count).Row
    End With
    Set Rng1 = .Range("AY1:AY" & lLastRow)
    Set rng2 = .Range("BK1:BK" & lLastRow)
    rng2.Value = Application.WorksheetFunction.Median(Rng1.Value)
End With
End Sub

 

[Christopher lee]

it gv unable to get the median property of the worksheet function class for the line :

rng2.Value = Application.WorksheetFunction.Median(Rng1.Value)

 

[Christopher lee]

and now my coding can be run already but it doesn't gv me a same ans in my column Iv....support median all the value it will same rite?but it gv me value but not all the row of column Iv are the same....u know y?

'' To Calculate the Median of U & V ETFintercept ''
Dim Rng1 As Range, Rng2 As Range
''uETFintercept''
Set Rng1 = Range(Range("AY1"), Range("AY" & Rows.count).End(xlUp).Offset(1))
Set Rng2 = Range(Range("AZ1"), Range("AZ" & Rows.count).End(xlUp).Offset(1))

''uETFintercept''
Cells((r + rowTO), IuTO) = Application.WorksheetFunction.Median(Rng1)
''vETFintercept''
Cells((r + rowTO), IvTO) = Application.WorksheetFunction.Median(Rng2)

 

[Teeroy]

What is in cell AY1?

 

[Christopher lee]

because AY1 is the column of the median i want....so i use AY1.....If i use AY only it gv me error again.....zzzz

 

[Christopher lee]

if can run but gv me a difference ans in the median column....i jz need to solve this problem then i done already....u hv any idea??

 

[Teeroy]

Everything I have been giving you has been tested on a representative workbook. There is obviously something strange in your workbook that I can't see.

If you want further help then share the workbook and I'll look at it directly but you are not answering the most basic questions I've asked.

With access to the workbook to test I doubt that this would have taken 10 minutes.