Evaluating a Formula via VBA

[reberryjr]

I have a record set with ~17K records, but I only need to see ~500 of them. I'm unable to alter the source of the data that I receive, so I'm trying to delete the rows I don't need, via VBA. I haven't tried using the Evaluate function before, and for some reason, I'm not able to get this to work. I've googled quite a bit, and the code below seems to match what others have used, but it's returning a #VALUE error.

Thoughts?

With dI.Range("T2", dI.Range("B" & Rows.Count).End(xlUp).Offset(, 18))
    '.Value = Evaluate("IF(ISNA(VLOOKUP(RC[,-10],BK_Issues,1,FALSE)),""N"",""Y"")")
    .Value = [IF(ISNA(VLOOKUP(RC[,-10],"BK_Issues",1,FALSE)),""N"",""Y"")")]
End With

 

[Domenic]

Try...

With dI
    With .Range("T2:T" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-10],BK_Issues,1,FALSE)),""N"",""Y"")"
        .Value = .Value
    End With
End With

Hope this helps!

 

[Rick Rothstein]

I have a record set with ~17K records, but I only need to see ~500 of them. I'm unable to alter the source of the data that I receive, so I'm trying to delete the rows I don't need, via VBA. I haven't tried using the Evaluate function before, and for some reason, I'm not able to get this to work. I've googled quite a bit, and the code below seems to match what others have used, but it's returning a #VALUE error.

Thoughts?

With dI.Range("T2", dI.Range("B" & Rows.Count).End(xlUp).Offset(, 18))
    '.Value = Evaluate("IF(ISNA(VLOOKUP(RC[,-10],BK_Issues,1,FALSE)),""N"",""Y"")")
    .Value = [IF(ISNA(VLOOKUP(RC[,-10],[B][COLOR="#FF0000"]"BK_Issues"[/COLOR][/B],1,FALSE)),""N"",""Y"")")]
End With

Assuming "BK_Issues" is a text string, you need to double up the quote marks surrounding it just the way you did for the "N" and "Y" text strings.

 

[reberryjr]

@Domenic Thank you! That worked!

 

[reberryjr]

@Rick Rothstein Thank you for the response. The "BK_Issues" is a named range, not a string. I see that I failed to include that in my problem statement...my apologies.