Large Formula VBA, but not working

[kpmsivaprakasam2003]

Hi, I am using Excel 2013,

I apply 2 way the Large Formula VBA, but not working, what i am wrong, i don't know

Sub Macro1()
'1st Table Dim statement ---> Declares variables
Dim lRow As Long, lCol As Long
lRow = Range("D5").End(xlDown).Row
lCol = Range("C5").End(xlToRight).Column

'Column Count in Number
Dim k As Long, m As Long
k = Range("C5", Range("C5").End(xlToRight)).Columns.Count
'Row Count in Number
m = Range("D6", Range("D6").End(xlDown)).Rows.Count

Dim MyRange As Range
Set MyRange = Range(Range("D5").Offset(1, k + 3), Range("D5").Offset(m, k + 3))

Range("D5").Offset(2, 1 + 3).Formula = "=LARGE(" & MyRange & ",1)"
'And
'=(LARGE($I$6:$I$12,1)-I7)/2
Range("D5").Offset(2, 1 + 3).Formula = "=(LARGE(" & MyRange & ",1)-" & Range("D5").Offset(1, k + 3) & ")/2"
End Sub

Kindy help me and Thanks for advance

 

[offthelip]

your problem is that the range object is very complex object all you want to put in the equation is the range address so change this line

Range("D5").Offset(2, 1 + 3).Formula = "=(LARGE(" & MyRange & ",1)-" & Range("D5").Offset(1, k + 3) & ")/2"

to

Range("D5").Offset(2, 1 + 3).Formula = "=(LARGE(" & MyRange.Address & ",1)-" & Range("D5").Offset(1, k + 3) & ")/2"

And similar every were else you are trying to address a range

 

[kpmsivaprakasam2003]

your problem is that the range object is very complex object all you want to put in the equation is the range address so change this line

Range("D5").Offset(2, 1 + 3).Formula = "=(LARGE(" & MyRange & ",1)-" & Range("D5").Offset(1, k + 3) & ")/2"

to

Range("D5").Offset(2, 1 + 3).Formula = "=(LARGE(" & MyRange.Address & ",1)-" & Range("D5").Offset(1, k + 3) & ")/2"

And similar every were else you are trying to address a range

Yes!!! Successfully work, thanks for your support