Link to home
Start Free TrialLog in
Avatar of William C Johnson
William C JohnsonFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If the name of either the worksheet or range is spelled incorrectly, you will generate a '1004 error message.
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
Avatar of William C Johnson

ASKER

This solution works every time.  Thanks for the quick response.