Increase row range by 1 in formula in VBA

[ZiggyZenLand]

I'm trying to pass the value of a formula to a cell, using VBA, without entering the formula in the cell; for several rows in the same column. I'm trying to use the Evaluate method but it only works using A1 style references; so I'm unable to use R1C1 style like the below code, which works perfectly but puts the formula in the cell and not just the values.

For i = 3 To LastRow
Range("R" & i).FormulaR1C1 = "=IFERROR(IF(RC[-11]>RC[-10],""T"",""F""),""ER"")"
Next i

Below I'm trying to use the Evaluate method but don't know how to increase the row number inside the formula each time the line of code is run. With the below code, I get the value for row 3 in all my rows.

For i = 3 To LastRow
Range("R" & i).Value = Evaluate("IFERROR(IF(G3>H3,""T"",""F""),""ER"")")
Next i

I really appreciate any assistance you can provide. Thank you.

Excel 2013

 

[Michael M]

Maybe this way

For i = 3 To lastrow
Range("R" & i).Value = Evaluate("IFERROR(IF(G" & i & ">H" & i & ",""T"",""F""),""ER"")")
Next i

 

[ZiggyZenLand]

That's awesome, thank you! It works perfectly for that code.

I tried applying the same method to a more complex formula and . If you could take a look at this, I would really appreciate it. The below code works fine with the Evaluate method for row 3. This tells me there's nothing wrong with the formula.

    For i = 3 To LastRow
        Range("R" & i).Value = Evaluate("IFERROR((INDEX(Lists!N:N,MATCH(1,(G3=Lists!B:B)*(H3=Lists!D:D),0))),""0"")")
    Next i

When I try to add the method you used above so it will work for all rows, the value returns 0 because my IFERROR tells it to when it finds an error. I can't see what the issue might be. I don't think it's the formula itself, maybe a misplaced coma or quotes. It's an Index Match formula looking at data from another sheet named Lists.

    For i = 3 To LastRow
        Range("R" & i).Value = Evaluate("IFERROR((INDEX(Lists!N:N,MATCH(1,(G" & i & "=Lists!B:B)*(H" & i & "=Lists!D:D),0))),""0"")")
    Next i

I appreciate your time, thank you.

 

[ZiggyZenLand]

I kept playing with it and finally got it to work. My eyes are tired but it looks like the same code above. If so, not sure what I did differently.

    For i = 3 To LastRow
         Range("R" & i).Value = Evaluate("IFERROR((INDEX(Lists!N:N,MATCH(1,(G" & i & "=Lists!B:B)*(H" & i & "=Lists!D:D),0))),""0"")")
    Next i

I really appreciate your help, thank you Michael M!

 

[Michael M]

Ok, glad you got it working....
I'd suggest with this later formula though to reduce your ranges to a finite value rather than the entire column...something like

For i = 3 To LastRow
         Range("R" & i).Value = Evaluate("IFERROR((INDEX(Lists!N1:N1000,MATCH(1,(G" & i & "=Lists!B1:B1000)*(H" & i & "=Lists!D1:D1000),0))),""0"")")
Next i