Error check and if the cell is not numeric or empty loop to the next item

**JamesGoulding85** · 07-23-2013, 09:11 AM

I have a loop set up to run a function. I need to be able to do some validation and if a cell is invalid it skips to the next loop. I have numerous error checks to do and should any of them be an error I want to skip to the next loop. I know its easy I just cant picture it

Thanks

**Richard Buttrey** · 07-23-2013, 09:15 AM

Hi,

Well at its simplest you need to preface the loop..end loop instruction with an IF test on the cell which might be invalid. If the test is succesful then the loop will run, if not control will bypass the loop to the End If statement.

In order to advise more I think we need to see the workbook.

**JosephP** · 07-23-2013, 09:15 AM

can't you just perform each test and only run the code if all pass?

if isnumeric(rCell) then
   if rcell.value2 > 100 then
    'et cetera
   end if
end if

**Solus Rankin** · 07-23-2013, 09:17 AM

Something like this:

If IsNumeric(Range("RangeImTryingToCheck")) = False _
        Or Range("RangeImTryingToCheck").Value = "" Then
    next loop
Else:
    'actions to preform here'
    next loop
End if

I could make it more precise if you wanted to attach your workbook.

**JamesGoulding85** · 07-23-2013, 09:23 AM

This is what I have:

For Each cell_Check In range_ValidRows

  
    '''set the variables required for the method and error check'''

If Not IsNumeric(cell_Check.Value) = False And cell_Check.Value > 0 Then
    int_FirstYear = cell_Check.Value
Else
    Next cell_Check
End If
If Not IsNumeric(cell_Check.Value) = False And cell_Check.Value > 0 Then
    int_FirstYear = cell_Check.Value
Else
    Next Cell_Check
End If

If Not IsNumeric(cell_Check.Offset(0, 1).Value) = False And cell_Check.Offset(0, 1).Value > 0 Then
    int_Frequency = cell_Check.Offset(0, 1).Value
Else
    Next Cell_Check
End If

If Not IsNumeric(cell_Check.Offset(0, 2).Value) = False And cell_Check.Offset(0, 2).Value > 0 Then
    int_Spread = cell_Check.Offset(0, 2).Value
Else
    Next Cell_Check
End If

If Not IsNumeric(cell_Check.Offset(0, -1).Value) = False And cell_Check.Offset(0, -1).Value > 0 Then
    long_Cost = cell_Check.Offset(0, -1).Value / cell_Check.Offset(0, 2).Value
Else
    Next cell_Check

'''Continues with the rest of the function code
End If

What I want to do is ensure these cell are valid if they are run the function on that row, if their not skip to the next row

**JosephP** · 07-23-2013, 09:27 AM

perhaps

   For Each cell_Check In range_ValidRows
   '''set the variables required for the method and error check'''
   
      If Application.WorksheetFunction.Count(cell_Check.Offset(, -1).Resize(4)) = 4 And Application.WorksheetFunction.CountIf(cell_Check.Offset(, -1).Resize(4), ">0") = 4 Then
         int_FirstYear = cell_Check.Value
         int_Frequency = cell_Check.Offset(0, 1).Value
         int_Spread = cell_Check.Offset(0, 2).Value
         long_Cost = cell_Check.Offset(0, -1).Value / cell_Check.Offset(0, 2).Value
         
         '''Continues with the rest of the function code
      End If
   Next cell_Check

**JamesGoulding85** · 07-23-2013, 10:06 AM

Hi Josie,

ive tried this but it doesn't work, if a letter is typed there is a miss match, and if a row is empty it doesn't complete the function

I don't fully understand how it works so couldn't try to alter it, if you could an explanation would be helpful

Thanks

**JosephP** · 07-23-2013, 10:07 AM

my bad-had the resize method wrong

If Application.WorksheetFunction.Count(cell_Check.Offset(, -1).Resize(, 4)) = 4 And Application.WorksheetFunction.CountIf(cell_Check.Offset(, -1).Resize(, 4), ">0") = 4 Then

**JamesGoulding85** · 07-23-2013, 10:13 AM

Excellent thanks Josie that works

but what is this magic you have used ive never come across error handling like that and I could use it in a load of places.

Regards

Jim

**JosephP** · 07-23-2013, 10:28 AM

that ain't magic ;-)

it just uses the COUNT worksheet function to make sure there are 4 numbers in the 4 cells and the COUNTIF function to make sure all 4 are greater than 0

**JamesGoulding85** · 07-23-2013, 10:45 AM

Ah that makes sense I like the use of resize I shall certainly be using that one in the future

Thanks for your help

**JamesGoulding85** · 07-23-2013, 10:47 AM

Ah that makes sense I like the use of resize I shall certainly be using that one in the future

Thanks for your help

Error check and if the cell is not numeric or empty loop to the next item

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Re: Error check and if the cell is not numeric or empty loop to the next item

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to write loop to check for empty cells

[SOLVED] Makro with if condition to check for numeric and text values in a cell range

[SOLVED] Loop Until Empty Cell

[SOLVED] Match Formula to Check if Cell Value is Within a Numeric Range Table

Do Loop for Error check

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions