• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

check for formula #value errors and abort the code if they exist

R

Rob Webster

New Member
Hi All,

does anyone know if it is possible to check a column to see if it has a #value error, and if it does, give the address of the cell where the first error exists in a message box, and then exit the rest of the code,

I have some code that deletes lines that contain zeros in a particular column, but the code crashes when it gets to lines that have errors in them. I need to identify and correct the errors rather than ignore them, so I don't want to just put error handling in the code
 
is that structured like the spread sheet function, i.e used with an IF (and then EndIF) statement? when I've tried to use it before its still just caused a run time, so I've probably not structured it correctly. I'll have another go with it though, thank you.
 
there's a lot of copy/pasting going on from several sheets, onto one consolidated sheet, and then the volume of data is reduced from the consolidated sheet by removing all rows that have a value of zero in column O from the data set.

the section of code that deletes the lines from the consolidated sheet is is as below, which works fine unless the original entry which is pasted (result) into Column O, had a value error.

Code: 
  Const colO  As Long = 15
  Dim FirstRow  As Long
  Dim LastRow  As Long

  LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
  FirstRow = 1

  Do While FirstRow <= LastRow
  If Cells(FirstRow, colO) = 0 Then
  Cells(FirstRow, 1).EntireRow.Delete
  LastRow = LastRow - 1
  Else: FirstRow = FirstRow + 1
  End If
  Loop

I wanted to check for error values on either the consolidated sheet (column 15 which I would think easiest solution) or in all of the original data before it runs so that the user gets a chance to correct the error and avoid the crash. to help the user correct the error it would be handy to know which sheet had the error and which row it was in before it exits the sub the first error it comes across would be ok as a starting point it doesn't need to be a full list of all areas where errors exist as they are few and far between, really its a prompt that they need to resolve issues before setting the code on its way.
 
Last edited by a moderator:
Hi Rob ,

You have made two points , and one of them does not seem to be a problem.

1. The code which you have anyway aborts when it comes across a #VALUE! error value in any cell in column O ; in what way will trapping this error and exiting gracefully change anything ?

2. You want that the code should check the input worksheets whose data is being copied and pasted into the Consolidated sheet ; for this , you need to indicate :
  • How many sheets are involved and what are their names ?
  • Which column is to be checked in these worksheets ; is the column the same in all the worksheets or is it different in different worksheets ?
  • Can this error testing code be incorporated into your copy + paste code , so that if an error is encountered , the copy + paste is not done ?
3. This is something that you can do using worksheet formulae ; for example , if the data is from column X in the input worksheets , in each worksheet , you can have a formula such as :

=ADDRESS(MIN(IF(ISERROR(X:X),ROW(X:X))),CODE("X") - CODE("A")+1)

This is an array formula , to be entered using CTRL SHIFT ENTER.

If there is no error value in column X , this formula will return the #VALUE! error value ! If there is an error value in the data in column X , the formula will give the address of the cell which contains the error value.

It is clear that the above formula should not be entered in any cell in column X.

Narayan
 
Hi Narayan,

Thank you for your reply, you have made a very valid point. I realise that my point (1) isn't very effective and its not going to give me a helpful solution, my code (above) is only deleting the lines, and at that point the address of the cell that the error would be in would be in its copied location, which is no use if i am to get the address of the original entry.

there are 6 separate sheets containing 1 table on each, the tables housing the source data. Each table contains a number of unique headings, and also some which are the same. My code extracts the data that is in the fields that are the same, from each table and consolidates it, into one sheet - the data set represents equipment which we hire out. When it is returned to us, we no longer need it in the consolidated list. we do need it in the source list because we are able to use that information to find out which items offer good investment opportunities for example and also for other things.

the information which is not required from the consolidated sheet is deleted because its value (imported from the source sheets) is zero. the column number that the values are in is different in each table, but it is consistently 3 away from the furthest on the right hand end. because it is in a table I could select the first header line, and do xl right, and then offset that by -3 columns, this would get me to the right column number. (albeit a little long!) I just use the sheet1, sheet2 names, because sometimes the users change the sheet names to reflect different types that are used in the different depots. and sometimes they add extra sheets, so each workbook can become individual.

each sheet is emailed to me (using a separate bit of code) and I add them all together to get an overall version for the company.

the address function isn't one I'm familiar with so i'll look that one up, Maybe I could add it a cell above the header row in each table to obtain the addresses, of errors, and return them in a message box, (and exiting the sub). If there are no errors, and the formula returns an error itself, then the message box would then be bypassed by the error handling that i assume would be necessary to avoid it crashing - does that logic reflect your comments? it's worth a go anyway as its not a route i've tried to date.

once again thank you very much, you have been a great help.

kind regards and best wishes.
 
You must log in or register to reply here.
Back
Top