Solved

Add "go to next" if error encountered

Posted on 2014-02-24
6
235 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Batch convert .doc to .docx 13 177
Access DB on open (All Objects) 5 61
Which version of Microsoft Project does my client need? 3 75
Button in Excel 5 75
With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now