Using VBA to sort through a range for first cell with error

[CS45]

Hi all,

I am working on an Excel project for work in which I need to write a macro that, among other things, searches through a range of cells and returns the first cell that has a #DIV/0 error.

That range calculates a division of cells within a pivot table. If the pivot table has 5 rows of data for a given filter, the division range will have 5 rows of values, then multiple rows of #DIV/0. Based on the filter, the pivot table can have anywhere from 0 to 50 rows of values, which is the reason for the extra division cells.

The division range starts at I7 and theoretically goes through Range("I7").End(xlDown), so how do I go through each cell in that range, check if it holds a value or an error, and stop at the first cell that has an error?

Thanks in advance!

 

[Gasman]

With Excel I always start of with recording a macro and develop it from there, if it is something I have not done before nor can I find via Google.
So see what that macro produces and work from there.?

Google Search

www.google.com

 

[Isaac]

This is one way, it depends that it will always eventually encounter an error within that column at some point - which you have mentioned is correct.
Some version of the Find method may be better, but this was what I came up with without much time to spend on it

Function FindFirstError() As Range
Dim ws As Worksheet, rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("I7")
Do Until IsError(rng) = True
    Set rng = rng.Offset(1, 0)
Loop
Set FindFirstError = rng
End Function

 

[Gasman]

Also the value will be 'Error 2007'

Cell Error Values

Office VBA reference topic

docs.microsoft.com

 

[Isaac]

Also the value will be 'Error 2007'

Cell Error Values

Office VBA reference topic

docs.microsoft.com

Oh yeah--I was focusing on OP last sentence 'an error' but I see they were more specific.
This then

Function FindFirstError() As Range
Dim ws As Worksheet, rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("I7")
Do Until rng.Text = "#DIV/0!"
    Set rng = rng.Offset(1, 0)
Loop
Set FindFirstError = rng
End Function