Solved

Add "go to next" if error encountered

Posted on 2014-02-24
6
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 

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 500 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Automatic sort formula 8 63
Best format for image notations in Book using Word 6 59
Calculate waiting times that spans two days 10 46
Help to break down spreadsheet 3 39
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

749 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