A quicker way to use a loop

Thanks cytop. Good tip.

This still loops row by row. What i was wondering is I have 30,000+ rows and maybe only 1,000 have NOT AVAILABLE. Is there a more efficient way to do so, maybe not?

bkeat, you could try a For each loop on a range. I'm not going to say one way or the other if it is faster than what you have, but there are definitely situations where either one may come out the winner. If you really don't want to iterate through every row, you could do something like have your code filter for values, then update those cells, but, behind the scenes, the compiler is going to have to loop through each row anyway to apply a filter.

Set Rng = Range("Y2:Y" & LastRow)

For Each c In Rng
    If c.Value = "Not Available" Then
            c.FormulaR1C1 = "FORMULA"
    End If
Next c

Greg