Use VBA to a create an If Statement in Excel
In this article, I try to present the VBA code to Excel If statement. The "if" statement is very useful to create and comparing the condition of situation. VBA help us to develop then condition procedure for the "if" statement. If statement helps to clearing the doubt in conditional operation.
Use VBA to a create an If Statement in Excel
The "if" statement works with worksheet in Excel. This condition use to change the value of another cell. For example, results are based on the obtain marks, the worksheet would put a "Pass" or "Fail" in the cell. We set the containing of the formula in Excel. This grade based on the Excel formula. But VBA also helpful for develop the code iof "if" Statement.
For marks 55,=if(a1>=50,"Pass","Fail")
For marks 45,=if(a2<50,"Fail","Pass")
Results.
55,Pass
45,Fail
Given step explain how to change the If formula in Excel to VBA procedure. .
How to Hand Complex formulas in statement If.
In this formula we set the formula to grade the scores into "Fail", "Pass" Or "A"; if student get a score of 70 or greater was an "A".
=IF(AND(A1>0,A1<50),"Fail",if(and(a1>=50,a1<70),"Pass",if(and(a1>=70,a1<=100),"A",)))
We can use the VBA to write formula in the Excel cells.
if a1>0 and a1<50,"Fail"
if a1>=50 and a1<70,"Pass"
if a1>=70 and a1<=100,"A"
Now we can use the VBA code to loop by the cells values into the text string . You need to use of "if" formula.
if(and(a1>0,a1<50),"Fail",next statement)
Range("a1").Select
Set rng = Range("a1:" & ActiveCell.End(xlDown).Address)
quot=chr(34)
myStr="="
// We can you maximum 34 character in quatation
For i = 1 To rng.Rows.Count
myStr = myStr & "if(and(" & Replace(rng.Rows(i), "and", ",")
myStr = myStr & ")," & quot & rng.Rows(i).Offset(0, 1) & quot & ","
Next
For i = 1 To rng.Rows.Count
myStr = myStr & ")"
Next
This is the statement format of Excel "if " Formula
=IF(AND(A1>0, A1<50),"Fail",IF(AND(A1>=50, A1<75),"Pass",IF(AND(A1>=75, A1<=100),"A",)))
The formula can be written in VBA the code .
Range("d2").Select
ActiveCell.Formula = myStr
Set rng = ActiveCell.CurrentRegion.Columns(1)
Selection.AutoFill Destination:=Range("d2:d" & rng.Rows.Count)
With the help of above procedure we can easily convert the Excel "if" statement in VBA code.