 # IF and Max Formula through VBA

• MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question IF and Max Formula through VBA
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())…
stackoverflow.com

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:…
chandoo.org 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:…
www.mrexcel.com

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())

Code
``````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())"``````

Edited once, last by Carim: Added Code Tags ().

• Hi,

First question : Is your formula working as expected when you input it directly in a cell ?

Second question : could your initial formula be simplified :

Code
``=IF(I6=\$L\$6,MAX(\$H\$6:\$H\$79),NA())``

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner Edited 3 times, last by Carim ().

• You're missing closing brackets for the ISNUMBER and VALUE functions.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!