Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add "go to next" if error encountered

Posted on 2014-02-24
6
Medium Priority
?
245 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

704 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