CODEWITHSUNDEEP
excelvbaif-statementrange
Dhruv Patel
Dhruv Patel

Reputation: 3

VBA If Statement Formula in a Loop and Range Function to Get Cell Value

I am trying to assign If formula to certain cell every 16th row. Formula needs to compare values of cells in J, K and L columns and gives Pass or Fail Result.

I am having issues making this If formula work in a loop. Please Help! My Code is below.

First I am getting compile error saying " Expected end of statement"

Second, is there any other way to make these work? I need same formula applied to Cell M5 to M789 at every 16th cell. 

For a = 5 To 789 Step 16

   Range("M" & a).FormulaR1C1 = "=IF(Range("J" & a)>= Range("K" & a),IF(Range("J" & a))<= Range("L" & a),""PASS"", ""FAIL""),""FAIL"")"

Upvotes: 0

Views: 2200

Answers (1)

BigBen
BigBen

Reputation: 50007

  1. You need .Formula instead of .FormulaR1C1 - you are not using R1C1 references.

  2. Remove the Range calls from the formula part. That's mixing VBA with regular worksheet formula syntax.

  3. You can simplify the formula using AND. And in this case you don't need to concatenate a in at all. Excel is smart enough to update relative references.

Sub Test()
    Dim myFormula As String
    myFormula = "=IF(AND(J5>=K5,J5<=L5),""PASS"",""FAIL"")"

    Dim a As Long
    For a = 5 To 789 Step 16
        Range("M" & a).Formula = myFormula
    Next
End Sub

Upvotes: 3

Related Questions