William C Johnson
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_Complet e1") 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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_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("CD D_Overall_ Complete1" ).Value < 1 Then
Month1_CDD_Team_Status
Red
End If
End Sub
I appreciate your help.
CowboyWM
Sub Test()
If Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Green
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Yellow
ElseIf Worksheets("Quarterly Refresh Progress").Range("CDD_Days
Month1_CDD_Team_Status
Red
End If
End Sub
I appreciate your help.
CowboyWM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
This solution works every time. Thanks for the quick response.