Improve company productivity with a Business Account.Sign Up

x
?
Solved

Add "go to next" if error encountered

Posted on 2014-02-24
6
Medium Priority
?
252 Views
Last Modified: 2014-02-25
Hi, I have the following code that colors a cell different colors based on the date compared to today in Column F. (red if it is in the past, yellow if it is today, etc.)

Recently had an issue with blanks and the value "NULL" in that field that cause the macro to crash. Need to modify this so that if it detects an error condition, it just goes to the next row instead of crashing.

Thanks in advance,

swjtx99

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
   
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
       
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
       
        C.Interior.ColorIndex = CI
    Next
0
Comment
Question by:swjtx99
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39883471
I've added on error statement...

and a "ContinueNext" label that should handle the error

Note : the "On Error GoTo 0" statement clears out the prior error.

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
    
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
        
        On Error GoTo ContinueNext
        
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
        
        C.Interior.ColorIndex = CI
ContinueNext:
        On Error GoTo 0
    Next

Open in new window

0
 

Author Comment

by:swjtx99
ID: 39883696
Hi Ken,

Thanks for the reply,

I replaced 2 of the dates with the word NULL and it still errors out here:

   Select Case Int(targetDate) - C

Any ideas?

Thanks,
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39883775
The "On Error GoTo ContinueNext" should have caught that.


Can you post the modified code you used?

I want to confirm the placement of "On Error Goto ContinueNext".
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:swjtx99
ID: 39883788
Hi Ken,

Sure:

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
   
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
       
        On Error GoTo ContinueNext
       
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
       
        C.Interior.ColorIndex = CI
ContinueNext:
        On Error GoTo 0
    Next
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 2000 total points
ID: 39884194
I didn't realize this... but apparently I needed to "Resume" otherwise VBA thinks I'm still in error trapping mode from the first error.

This should work:

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
    
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
        
        On Error GoTo ContinueNext
        
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
        
        C.Interior.ColorIndex = CI
ContinueNext:
        Err.Clear
        Resume ContinueNext2
ContinueNext2:
    Next

Open in new window

0
 

Author Closing Comment

by:swjtx99
ID: 39886137
Thanks for your help.

Works great!

swjtx99
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question