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

Question

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

1) You need .Formula instead of .FormulaR1C1. 2) Don't use Range within a formula - that's mixing VBA into regular worksheet formulas. You can't use Range(J5) in a worksheet - just J5. — BigBen
– BigBen, Commented Nov 7, 2019 at 15:43
Thanks. Instead of Range, what else can I use to get values of J5 to J789, K5 to K789 and L5 to L789 cells within a loop? — Dhruv Patel
– Dhruv Patel, Commented Nov 7, 2019 at 15:45
Just "J", "K", and "L" - drop the Range calls from the formula part. — BigBen
– BigBen, Commented Nov 7, 2019 at 15:45
Range("M" & a).Formula = "=IF(("J" & a)>=("K" & a),IF(("J" & a)<= ("L" & a),""PASS"", ""FAIL""),""FAIL"")" OK i removed the Range part and still gives me compile error for "expected end of statement" with "J" highlighted. — Dhruv Patel
– Dhruv Patel, Commented Nov 7, 2019 at 15:50
One could also ask the question if you really need these cells to be filled with a formula. What purpose does it have. Are J, K and L column values going to change? Otherwise you are doing unnecessary calculations. — JvdV
– JvdV, Commented Nov 7, 2019 at 16:02

BigBen · Accepted Answer · 2019-11-07 16:02:49Z

3

You need .Formula instead of .FormulaR1C1 - you are not using R1C1 references.
Remove the Range calls from the formula part. That's mixing VBA with regular worksheet formula syntax.
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

edited Nov 7, 2019 at 16:02

answered Nov 7, 2019 at 15:57

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related