| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF AND THEN Statement problem
Hello-
I am having trouble getting the "then" portion of my statement not to happen when both IF conditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next If Range("J29:K29").Value 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" End If myCell.Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
Hey Scott,
The problem is that you are asking for a single value from multiple cells. You'll get a 'type mismatch' error. Are you trying to sum values J29:K29? "Scott" wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when both IF conditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next If Range("J29:K29").Value 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" End If myCell.Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
On Jun 19, 8:58*pm, StumpedAgain
wrote: Hey Scott, The problem is that you are asking for a single value from multiple cells.. * You'll get a 'type mismatch' error. *Are you trying to sum values J29:K29? "Scott" wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when both IF conditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next If Range("J29:K29").Value 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" End If myCell.Select End Sub- Hide quoted text - - Show quoted text - Yes, I need the sum of J29:K29 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
On Jun 19, 8:58*pm, StumpedAgain
wrote: Hey Scott, The problem is that you are asking for a single value from multiple cells.. * You'll get a 'type mismatch' error. *Are you trying to sum values J29:K29? "Scott" wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when both IF conditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next If Range("J29:K29").Value 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" End If myCell.Select End Sub- Hide quoted text - - Show quoted text - Yes, I need the sum of J29:K29 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
If Sum("J29:K29") 3600 And Range("B6").Value 21600 Then
Mike F "Scott" wrote in message ... On Jun 19, 8:58 pm, StumpedAgain wrote: Hey Scott, The problem is that you are asking for a single value from multiple cells. You'll get a 'type mismatch' error. Are you trying to sum values J29:K29? "Scott" wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when both IF conditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next If Range("J29:K29").Value 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" End If myCell.Select End Sub- Hide quoted text - - Show quoted text - Yes, I need the sum of J29:K29 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
On Jun 20, 4:01*am, "Mike Fogleman" wrote:
*IfSum("J29:K29") 3600 And Range("B6").Value 21600Then Mike F"Scott" wrote in message ... On Jun 19, 8:58 pm, StumpedAgain wrote: Hey Scott, The problem is that you are asking for a single value from multiple cells. You'll get a 'type mismatch' error. Are you trying to sum values J29:K29? "Scott" wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when bothIFconditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next IfRange("J29:K29").Value 3600 And Range("B6").Value 21600Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" EndIf myCell.Select End Sub- Hide quoted text - - Show quoted text - Yes, I need the sum of J29:K29- Hide quoted text - - Show quoted text - I am getting an error when I use the line suggested, If Sum("j29:k29") 3600 And Range("B6").Value 21600 Then The message says "compile error, can't find project or library" and the word SUM in the code above is highlighted. Any ideas? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
Sum() is not a valid vba method as far as I know.
As long as you are only adding the two cells I would just create a new variable to add the ranges. The code below assumes that the seconds values in cells J29, K29, and B6 are all whole numbers. If your telephony system reports fractions of seconds and that carries over to your spreadsheet you should change the variable type. Private Sub Breaks() Dim ws As Worksheet Set ws = Worksheets("AgentReport") Dim lngBreakLunchSum As Long lngBreakLunchSum = Range("J29").Value + Range("K29").Value On Error Resume Next If lngBreakLunchSum 3600 And Range("B6").Value 21600 Then Range("d21").Value = ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf lngBreakLunchSum < 3600 And Range("B6").Value 21600 Then Range("d21").Value = "" End If End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
Also, I should note that I removed the myCell refernces because this
will not move the selected cell at all. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
I'm not sure if your if/then/elseif is really what you want, but this may get
you closer on the syntax: Option Explicit Private Sub Breaks() Dim ws As Worksheet Set ws = Worksheets("AgentReport") With ws If Application.Sum(.Range("J29:K29")) 3600 _ And .Range("b6").Value 21600 Then .Range("D21").Value = .Range("d3").Value & ", please make" _ & " sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Application.Sum(.Range("j29:K29")) < 3600 _ And .Range("B6").Value 21600 Then .Range("d21").ClearContents End If End With End Sub Scott wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when both IF conditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next If Range("J29:K29").Value 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" End If myCell.Select End Sub -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF AND THEN Statement problem
If Application.WorksheetFunction.Sum(Range("J29:K29") ) 3600
Mike F "Scott" wrote in message ... On Jun 20, 4:01 am, "Mike Fogleman" wrote: IfSum("J29:K29") 3600 And Range("B6").Value 21600Then Mike F"Scott" wrote in message ... On Jun 19, 8:58 pm, StumpedAgain wrote: Hey Scott, The problem is that you are asking for a single value from multiple cells. You'll get a 'type mismatch' error. Are you trying to sum values J29:K29? "Scott" wrote: Hello- I am having trouble getting the "then" portion of my statement not to happen when bothIFconditions are not met. The value of ("J29:K29") is 3590 and B6 is 31600. Since both conditions are not met, I dont want the message to appear in D21, but it does regardless. Private Sub Breaks() Dim myCell As Range Set myCell = Selection Dim ws As Worksheet Set ws = Worksheets("AgentReport") On Error Resume Next IfRange("J29:K29").Value 3600 And Range("B6").Value 21600Then Range("d21").Select ActiveCell.FormulaR1C1 = "" & ws.Range("d3").Value & ", please make sure to keep your Break/Lunch time under 1 hour. Thank you." ElseIf Range("J29:K29").Value < 3600 And Range("B6").Value 21600 Then Range("d21").Select ActiveCell.FormulaR1C1 = "" EndIf myCell.Select End Sub- Hide quoted text - - Show quoted text - Yes, I need the sum of J29:K29- Hide quoted text - - Show quoted text - I am getting an error when I use the line suggested, If Sum("j29:k29") 3600 And Range("B6").Value 21600 Then The message says "compile error, can't find project or library" and the word SUM in the code above is highlighted. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statement problem | Excel Worksheet Functions | |||
| IF statement problem | Excel Worksheet Functions | |||
| I have an "IF"Statement problem...please help!! | Excel Worksheet Functions | |||
| Problem with my Else statement | Excel Programming | |||
| IF statement problem | Excel Programming | |||
Linear Mode
