Using VBA to sort through a range for first cell with error (1 Viewer)

CS45

Member
Local time
Today, 09:37
Joined
Jun 17, 2020
Messages
38
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

Enthusiastic Amateur
Local time
Today, 14:37
Joined
Sep 21, 2011
Messages
7,163
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.?

 

Isaac

Lifelong Learner
Local time
Today, 06:37
Joined
Mar 14, 2017
Messages
2,769
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

Code:
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
 

Isaac

Lifelong Learner
Local time
Today, 06:37
Joined
Mar 14, 2017
Messages
2,769
Also the value will be 'Error 2007'

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

Code:
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
 

Users who are viewing this thread

Top Bottom