PDA

View Full Version : [SOLVED] If range contans "0", mark as incomplete



brent.fraser
05-27-2016, 09:56 AM
Hi all,

I know that this is simple (maybe not) but I have a report that has over 900,000 rows and I need to figure out what is complete and what is not.

I will use a for loop to loop through all of the rows and in each row, I have columns (H:AG) that will either contain a "1" or a "0." If any of these columns contain a "0" then that row item isn't complete.

Is there a way to check in it's entirety that H:AG contains or doesn't contain a "0"? I have seen how to do it on a cell by cell instance but not a "snapshot" of the entire range.

Still plugging away on it....

Thanks.

mdmackillop
05-27-2016, 10:48 AM
Something like this will put Incomplete in column F

Sub Test()
Dim r As Range
Set r = Range(Cells(1, "F"), Cells(900000, "F"))
With r
.FormulaR1C1 = "=COUNTIF(RC[2]:RC[27],0)>0"
.Value = .Value
.Replace What:=False, Replacement:=""
.Replace What:=True, Replacement:="Incomplete"
End With
End Sub

brent.fraser
05-31-2016, 07:52 AM
Thanks for the code MD...

Worked out very well. Now I am able to parse all 900,000 rows in a simple action.

Have a good one!

Brent