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 VBA example I gave earlier gave the median of a range From AY2:AY(end of column) after you change uETFinterceptTO to a value of 51.

 

[Christopher lee]

yr coding cant be run even i change the value to 51 also.....

 

[Christopher lee]

''Wv''
Cells((r + rowTO), WvTo) = CStr((Round(Cells(r + rowTO, ETFtargetUto), 4)) - ((Sheets("TFimport").Cells(n, 7)) * (Cells(r + rowTO, ETFtargetCto))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Round(Cells(r + rowTO, ETFtargetVto), 4))) + (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 28)) * (Cells(r + rowTO, ETFtargetCto))) + (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 33)) * (Cells(r + rowTO, ETFtargetCto) ^ 2))) / ((Sheets("TFimport").Cells(n, 6)) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 27) + 1E-19)))
''Wu''
Cells((r + rowTO), WuTo) = CStr((Round(Cells(r + rowTO, ETFtargetUto), 4)) / (Sheets("TFimport").Cells(n, 5))) - (((Sheets("TFimport").Cells(n, 7)) * (Cells(r + rowTO, ETFtargetCto))) / (Sheets("TFimport").Cells(n, 5))) - (((Sheets("TFimport").Cells(n, 6) + 1E-19) / (((Sheets("TFimport").Cells(n, 5)) * ((Sheets("TFimport").Cells(n, 6))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) ^ 2) / (Sheets("TFimport").Cells(n, 26) + 1E-19))) * (Sheets("TFimport").Cells(n, 27) + 1E-19))) * ((Round(Cells(r + rowTO, ETFtargetVto), 4)) - ((Sheets("TFimport").Cells(n, 7)) * (Cells(r + rowTO, ETFtargetCto))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * ((Round(Cells(r + rowTO, ETFtargetVto), 4)) - ((Sheets("TFimport").Cells(n, 28)) * (Cells(r + rowTO, ETFtargetCto))) - ((Sheets("TFimport").Cells(n, 33)) * (Cells(r + rowTO, ETFtargetCto) ^ 2))))))
'' Yv ''
Cells((r + rowTO), YvTo) = CStr((1 - ((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19))) / ((Sheets("TFimport").Cells(n, 6)) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 27) + 1E-19))))
''Yu''
Cells((r + rowTO), YuTo) = CStr(((Sheets("TFimport").Cells(n, 6) + 1E-19) / (((Sheets("TFimport").Cells(n, 5)) * ((Sheets("TFimport").Cells(n, 6))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) ^ 2) / (Sheets("TFimport").Cells(n, 26) + 1E-19))) * (Sheets("TFimport").Cells(n, 27) + 1E-19))) * (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) - 1) + (1 / Sheets("TFimport").Cells(n, 5)))
'' To Calculate the Median of U & V ETFintercept ''
''uETFintercept''
Cells((r + rowTO), IuTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, uETFinterceptTO), 6))))
''vETFintercept''
Cells((r + rowTO), IvTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, vETFinterceptTO), 6))))

Cells((r + rowTO), U_FILMTo) = CStr(((Round(Cells(r + rowTO, WuTo), 4) - ((Round(Cells(r + rowTO, YuTo), 4)) * (Round(Cells(r + rowTO, IuTo), 4))))))

Cells((r + rowTO), V_FILMTo) = CStr(((Round(Cells(r + rowTO, WvTo), 4) - ((Round(Cells(r + rowTO, YvTo), 4)) * (Round(Cells(r + rowTO, IvTo), 4))))))

r = r + 1
Loop

End If


This is my coding.....

 

[Teeroy]

That still appears to only be part of the code as you've go a Loop without a Do Statement.

It seems like you are trying to fill down the results of calculated values against a list of numbers but you've gone about it in a confusing way. It would be better to put a formula into each cell in one row then fill down. If you then want to replace the formula with its calculated value then you can use;

Range(cells(2,x),cells(lEnd,x)).value = Range(cells(2,x),cells(lEnd,x)).value

 

[Christopher lee]

ya that is what i am trying to doing nw....i want to get the result row by row by calculate the median of the whole column of uETFintercept..... i can get the results already nw but the result is doing row by row not help me to calculate the median of the whole column....that i stuck nw.....t.t....

 

[Christopher lee]

means i can add u this coding into my coding??like:

''uETFintercept''

Range(cells(2,x),cells(lEnd,x)).value = Range(cells(2,x),cells(lEnd,x)).value</pre>
Cells((r + rowTO), IuTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, uETFinterceptTO), 6))))
''vETFintercept''
Cells((r + rowTO), IvTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, vETFinterceptTO), 6))))

Cells((r + rowTO), U_FILMTo) = CStr(((Round(Cells(r + rowTO, WuTo), 4) - ((Round(Cells(r + rowTO, YuTo), 4)) * (Round(Cells(r + rowTO, IuTo), 4))))))

Cells((r + rowTO), V_FILMTo) = CStr(((Round(Cells(r + rowTO, WvTo), 4) - ((Round(Cells(r + rowTO, YvTo), 4)) * (Round(Cells(r + rowTO, IvTo), 4))))))

r = r + 1
Loop

End If

like this??

 

[Teeroy]

How about you share the file and I can see the data format you are trying to process. From that I may be able to suggest a more efficient way.

 

[Christopher lee]

Dim MedianUinterceptTo As String
Dim MedianUintercept As String


''Wv''
Cells((r + rowTO), WvTo) = CStr((Round(Cells(r + rowTO, ETFtargetUto), 4)) - ((Sheets("TFimport").Cells(n, 7)) * (Cells(r + rowTO, ETFtargetCto))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Round(Cells(r + rowTO, ETFtargetVto), 4))) + (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 28)) * (Cells(r + rowTO, ETFtargetCto))) + (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 33)) * (Cells(r + rowTO, ETFtargetCto) ^ 2))) / ((Sheets("TFimport").Cells(n, 6)) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 27) + 1E-19)))
''Wu''
Cells((r + rowTO), WuTo) = CStr((Round(Cells(r + rowTO, ETFtargetUto), 4)) / (Sheets("TFimport").Cells(n, 5))) - (((Sheets("TFimport").Cells(n, 7)) * (Cells(r + rowTO, ETFtargetCto))) / (Sheets("TFimport").Cells(n, 5))) - (((Sheets("TFimport").Cells(n, 6) + 1E-19) / (((Sheets("TFimport").Cells(n, 5)) * ((Sheets("TFimport").Cells(n, 6))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) ^ 2) / (Sheets("TFimport").Cells(n, 26) + 1E-19))) * (Sheets("TFimport").Cells(n, 27) + 1E-19))) * ((Round(Cells(r + rowTO, ETFtargetVto), 4)) - ((Sheets("TFimport").Cells(n, 7)) * (Cells(r + rowTO, ETFtargetCto))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * ((Round(Cells(r + rowTO, ETFtargetVto), 4)) - ((Sheets("TFimport").Cells(n, 28)) * (Cells(r + rowTO, ETFtargetCto))) - ((Sheets("TFimport").Cells(n, 33)) * (Cells(r + rowTO, ETFtargetCto) ^ 2))))))
'' Yv ''
Cells((r + rowTO), YvTo) = CStr((1 - ((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19))) / ((Sheets("TFimport").Cells(n, 6)) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) * (Sheets("TFimport").Cells(n, 27) + 1E-19))))
''Yu''
Cells((r + rowTO), YuTo) = CStr(((Sheets("TFimport").Cells(n, 6) + 1E-19) / (((Sheets("TFimport").Cells(n, 5)) * ((Sheets("TFimport").Cells(n, 6))) - (((Sheets("TFimport").Cells(n, 5) + 1E-19) ^ 2) / (Sheets("TFimport").Cells(n, 26) + 1E-19))) * (Sheets("TFimport").Cells(n, 27) + 1E-19))) * (((Sheets("TFimport").Cells(n, 5) + 1E-19) / (Sheets("TFimport").Cells(n, 26) + 1E-19)) - 1) + (1 / Sheets("TFimport").Cells(n, 5)))
'' To Calculate the Median of U & V ETFintercept ''
''uETFintercept''
Cells((r + rowTO), IuTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, uETFinterceptTO), 6))))
''vETFintercept''
Cells((r + rowTO), IvTo) = Application.WorksheetFunction.Median(CStr((Round(Cells(r + rowTO, vETFinterceptTO), 6))))

Cells((r + rowTO), U_FILMTo) = CStr(((Round(Cells(r + rowTO, WuTo), 4) - ((Round(Cells(r + rowTO, YuTo), 4)) * (Round(Cells(r + rowTO, IuTo), 4))))))

Cells((r + rowTO), V_FILMTo) = CStr(((Round(Cells(r + rowTO, WvTo), 4) - ((Round(Cells(r + rowTO, YvTo), 4)) * (Round(Cells(r + rowTO, IvTo), 4))))))

If (Round(Cells(r + rowTO, IuTo), 4)) = 0 Then
Cells((r + rowTO), U_FILMTo) = Null
End If

If (Round(Cells(r + rowTO, IvTo), 4)) = 0 Then
Cells((r + rowTO), V_FILMTo) = Null
End If




r = r + 1
Loop

End Ifthis is the coding i can share it....if cant then nvm already....thk for hlp....^^

 

[Teeroy]

You keep trying to calculate the median of a cell. Your code has lots of variable that you don't discloses so I have no idea how this relates to your data.

Getting back to the basics of your question:

What column is uETFintercept (in letters)?
What row does the data start in?
Where do you want the calculated median to be placed (in A1 format)?

 

[Christopher lee]

What column is uETFintercept ?
(AY)
What row does the data start in?
AY1, until end of the line of the data
Where do you want the calculated median to be placed (in A1 format)?
Column (BK)