Test if Cell range contains error

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,160
I'm trying to test if a cell range that has a formula in it shows an error. I've tried this code:

Code:
If BudSht.Range("CheckValue") = Error Then Exit Sub
i'm getting a type mismatch debug, even though the cell range shows to have an 'Error 2023'

is there a way to test if a cell has an error in it in vba?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Could you use a UDF such as:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> HasError(Rng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>   <SPAN style="color:#00007F">Dim</SPAN> C <SPAN style="color:#00007F">As</SPAN> Range<br>   <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> C <SPAN style="color:#00007F">In</SPAN> Rng.Cells<br>      <SPAN style="color:#00007F">If</SPAN> Application.WorksheetFunction.IsError(C) <SPAN style="color:#00007F">Then</SPAN> HasError = <SPAN style="color:#00007F">True</SPAN><br>   <SPAN style="color:#00007F">Next</SPAN> C<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,082
Office Version
365, 2010
Platform
Windows
I'm trying to test if a cell range that has a formula in it shows an error. I've tried this code:

Code:
If BudSht.Range("CheckValue") = Error Then Exit Sub
i'm getting a type mismatch debug, even though the cell range shows to have an 'Error 2023'

is there a way to test if a cell has an error in it in vba?
Code:
If IsError(BudSht.Range("CheckValue"))  Then Exit Sub
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,495
Messages
5,511,665
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top