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

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

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

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

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

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?

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

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.

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

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?