Test for 2 Conditions

I need to verify the data in two different cells before taking action.  This macro produces a Run-time error '1004'  Application-defined or object-defined error.  The value for Range("CDD_Overall_Complete1") is a percent field.  The value I am looking for is less than 100%.

Sub Test()

    If Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value < 16 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value <= 1 Then
        Month1_CDD_Team_Status
        Green
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 16 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 17 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 18 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 19 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 20 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 21 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 22 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 23 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 24 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 25 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 26 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 27 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 28 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 29 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value = 30 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Yellow
    ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value > 30 And Worksheets("Calculations").Range("CDD_Overall_Complete1").Value < 1 Then
        Month1_CDD_Team_Status
        Red
    End If
End Sub

I appreciate your help.

CowboyWM
cowboywmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

why don't you use a case statement for the first condition? Something along these lines:

Sub test()

 If Worksheets("Calculations").Range("CDD_Overall_Complete1").Value <= 1 Then
        Select Case Worksheets("Quarterly Refresh Progress").Range("CDD_Days_Gone_By1").Value
            Case Is < 16
                Month1_CDD_Team_Status
                Green
            Case 16 To 30
                Month1_CDD_Team_Status
                Yellow
            Case Is > 30
                Month1_CDD_Team_Status
                Red
          End Select
End If

End Sub

Open in new window



If that presents an error, at least it will be easier to troubleshoot.

cheers, teylyn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
If the name of either the worksheet or range is spelled incorrectly, you will generate a '1004 error message.
0
rspahitzCommented:
It appears that either CDD_Days_Gone_By1 or CDD_Overall_Complete1 is not a defined Range/Cell in Excel.
To find it, click on the name box (just above the "A" above cell A1, and click the dropdown arrow next to the box.  If the name is not found (or not spelled the same), you will get this error (as indicated by byundt)

If you need to check for this in your code, find I would recommend the improvements suggested by teylyn, then add something like this:

Sub Test
  On Error Goto Test_Err
...
Test_Exit:
  Exit Sub
 
Test_Err:
  MsgBox "Error found in code: " & Err.Description
  Resume Test_Exit
End sub
0
cowboywmAuthor Commented:
This solution works every time.  Thanks for the quick response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.