IF and Max Formula through VBA

[kpmsivaprakasam2003]

Hi, I am using Excel 2013

I have applied the VBA formula but not return the results

Below formula:
=IF(ISNUMBER(I6),IF(VALUE(I6)=$L$6,MAX($H$6:$H$79),NA()),NA())

VBA:
Dim MyRange As Range
Set MyRange = Range("D5").Offset(1, 1 + 3).Resize(m + 1, 1)
Range("D5").Offset(1, 1 + 6).Formula = "=IF(ISNUMBER(" & Range("D5").Offset(1, 1 + 4).Address(0, 0) & ",IF(VALUE(" & Range("D5").Offset(1, 1 + 4).Address(0, 0) & "=" & Range("D5").Offset(1, 3 + 5).Address(1, 1) & ",MAX(" & MyRange.Address & "),NA()),NA())"

Thanks for Advance

 

[bebo021999]

Like this:

Set MyRange = Range("H6").Resize(m + 1, 1)
If IsNumeric(Range("I6")) And Range("I6") = Range("L6") Then
    Range("J6").Value = WorksheetFunction.Max(MyRange)
Else
    Range("J6").Value = "NA()"
End If

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

IF and Max Formula through VBA

Hi, I am using Excel 2013 I have applied the VBA formula but not return the results Below formula: =IF(ISNUMBER(I6),IF(VALUE(I6)=$L$6,MAX($H$6:$H$79),NA()),NA()) VBA: >>> as You've noted already few times > use code - tags

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places (and you have), please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.