IF AND THEN Statement problem

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
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?
 
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 -
Click to expand...

Yes, I need the sum of J29:K29
 
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 -
Click to expand...

Yes, I need the sum of J29:K29
 
 IfSum("J29:K29") > 3600 And Range("B6").Value > 21600Then







Yes, I need the sum of J29:K29- Hide quoted text -

- Show quoted text -
Click to expand...

I am getting an error when I use the line suggested, If Sum("j29:k29")
3600 And Range("B6").Value > 21600 Then
Click to expand...

The message says "compile error, can't find project or library" and
the word SUM in the code above is highlighted. Any ideas?
 
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
 
Also, I should note that I removed the myCell refernces because this
will not move the selected cell at all.
 
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
 
Back
Top