IF AND THEN Statement problem

[Scott]

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

 

[StumpedAgain]

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]

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?










- Show quoted text -

Yes, I need the sum of J29:K29

 

[Scott]

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?










- Show quoted text -

Yes, I need the sum of J29:K29

 

[Mike Fogleman]

If Sum("J29:K29") > 3600 And Range("B6").Value > 21600 Then

Mike F

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?










- Show quoted text -

Yes, I need the sum of J29:K29

 

[Scott]

 IfSum("J29:K29") > 3600 And Range("B6").Value > 21600Then







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?

 

[Aviashn]

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

 

[Aviashn]

Also, I should note that I removed the myCell refernces because this
will not move the selected cell at all.

 

[Dave Peterson]

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

 

[Mike Fogleman]

If Application.WorksheetFunction.Sum(Range("J29:K29")) > 3600

Mike F

IfSum("J29:K29") > 3600 And Range("B6").Value > 21600Then







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?