Nested IF(AND( statements in VBA, can it be done?

[JBShandrew]

How do I get this formula to work in VBA without creating a Sub()?

.Range("M33").Value = "=IF(L33<2,"Min 2 tests",IF(AND(L33>3,L33<4),"4 TESTS",IF(AND(L33>4,L33<6),"5 tests","More than five test are necessary per LIFT")))

I'm trying to use it in conjunction with a "With Statement"

These work very well, Thanks to Jake's expert help.

.Range("L31").Value = "=($G$32*$I$32*$K$32)/9"
.Range("L33").Value = "=($L$31/5000)"
.Range("M31").Value = "Test every 5,000 SQ Yards"

With one of Mr. Spreedsheets books, I get an example of using If-Then-Else, but not for this many variables: and not in this format, can this work in a Range statement to assign this formula to a specific cell?

 

[VoG]

Try

.Range("M33").Formula = "=IF(L33<2,""Min 2 tests"",IF(AND(L33>3,L33<4),""4 TESTS"",IF(AND(L33>4,L33<6),""5 tests"",""More than five test are necessary per LIFT"")))"

 

[ukmikeb]

Hi

Vog beat me to the draw.

However, will this condition ever be satisfied -
IF(AND(L33>3,L33<4)

And, isn't it easier to write this condition -
IF(AND(L33>4,L33<6) as IF(L33=5

hth

 

[JBShandrew]

Try

.Range("M33").Formula = "=IF(L33<2,""Min 2 tests"",IF(AND(L33>3,L33<4),""4 TESTS"",IF(AND(L33>4,L33<6),""5 tests"",""More than five test are necessary per LIFT"")))"

Thak you this worked very well. Just when question more. Do all formulas require double "" markes before and after a logic evaluation?


I'm nearly 50 years old just learning how much can be done in Excel, and what the advantages to useing VBA are. I am very gratful to those of you who are true experts in Excel and VBA.

Sincerely,

J.B.

 

[JBShandrew]

Hi

Vog beat me to the draw.

However, will this condition ever be satisfied -
IF(AND(L33>3,L33<4)

And, isn't it easier to write this condition -
IF(AND(L33>4,L33<6) as IF(L33=5

hth

Yes this condition will be satisfied by the number that is in L33, which is put in place by VBA also, based off of VBA calculations.

So far though I am limited in VBA to Recording Macros, and using .Range ("XX") . Value = "=()" now .Range . Formula = "=IF(AND("")""

It would be easier to write as if you have written. However, in this case I need to evaluate a range to determine the amount of field tests that I need to take per lift, per day.

 

[VoG]

If you enter a formula in a cell manually then you use single "s

However, if you are entering the formula using VBA then you need to double up all the " within the formula.

 

[JBShandrew]

If you enter a formula in a cell manually then you use single "s

However, if you are entering the formula using VBA then you need to double up all the " within the formula.

Thank you for the lesson.

Sincerely,

J.B.

 

[JBShandrew]

Syntax help please?

I am trying to include in a .Range . Formula the Rand() function using the F9 key by adding the below code. Please tell me what I am doing wrong?

.Range("G10").Formula = "=IF($J4=4,RAND()vbKeyF9,"""")"

Thank you in advance.

J.B.

 

[VoG]

Perhaps you mean

.Range("G10").Formula = "=IF($J4=4,RAND(),"""")"
.Range("G10").Calculate

 

[JBShandrew]

Perhaps you mean

.Range("G10").Formula = "=IF($J4=4,RAND(),"""")"
.Range("G10").Calculate

I'm trying to get the same result as when I use =RAND()F9 to create a static random number, but can not seem to get the syntax to work for me. and F1 only give me a list of the Keys, but not how to use them in a formula.

What I have tried.

.Range("G10").Formula = "=IF($J4=4,RAND()vbKeyF9,"""")"
.Range("G10").Formula = "=IF($J4=4,RAND().vbKeyF9,"""")"
.Range("G10").Formula = "=IF($J4=4,RAND() {vbKeyF9},"""")"