"If SUM <" in VBA

[xzmike]

I'm tryingi to write in code

if the sum of cell A2 thru A4 is not greater than 7 then....

I'm not sure when to use quotes around constants for the
IF SUM( &address..etc < 7

thanks for any help
Mike

 

[Tazguy37]

How about?:

'if the sum of cell A2 thru A4 is not greater than 7 then....
    If Application.WorksheetFunction.Sum(Range("A2:A4")) < 7 Then MsgBox "Sum less than 7"

Hope that helps!

 

[xzmike]

Reply to "If Sum <" in VBA

I should have made myself more clear. The columns can vary. Here it may be A2.A4. The nex time in my loop it may be B2.B4.

Thanks for any help.
Mike

 

[Tazguy37]

Then you could use an inputbox, for the user to enter a range:

Sub test7a()
Dim myRange As Range

    Set myRange = Application.InputBox("Enter range to sum:", , , , , , , 8)
    If Application.WorksheetFunction.Sum(myRange) < 7 Then MsgBox "Sum less than 7"

End Sub

HTH

 

[xzmike]

Still getting type mismatch error

Here's some of the code I'm trying to run. I'm looping thru A1 to C1, checking the totals of row 3 thru 5 to see if the total is equal to 7 . I don't know if my TimeOff should be a Range or a Double. I'm getting type mismatch whenever I try either.

Dim rng As Range
Dim rngTarget As Range
Dim rngTimeOff As Range

Set rngTarget = Range("a1:c1")
For Each rng In rngTarget
Set rngTimeOff = "SUM(" & rng.Offset(2, 0).Address & ":" & rng.Offset(4, 0).Address & ")"
If rngTimeOff = 7 Then MsgBox "Time Off is seven"
Next

thanks for any help
Mike

 

[Nimrod]

Public Sub TestForSumOf7()
Dim SumRng As Range

'SET TOP AND BOTTOM ROWS THAT YOU WANT SUM'd
TopRow = 3
BottomRow = 5

'LOOP THRU EACH COLUMN THAT YOU WANT SUM'd
For Col = 1 To 3

    ' SET THE RANGE to the CURRENT COLUMN and SPECEFIED ROWS
    Set SumRng = Range(Cells(TopRow, Col), Cells(BottomRow, Col))
    
    'TEST SUM WITHIN SPECIFIED RANGE of CURRENT COLUMN
    If WorksheetFunction.Sum(SumRng) = 7 Then _
    MsgBox "Time Off is seven for " & SumRng.Address

Next Col
End Sub

 

[xzmike]

Getting error using range addrss

Nimrod, I tried using your formula with a range address and got a "type mismatch" in the line Set rngTimeOff. Can I use that code there?

Thanks for any help
Mike

Sub TestFor7()

Dim rng As Range
Dim rngTarget As Range
Dim wks As Worksheet
Dim rngTimeOff As Range

Set wks = ActiveSheet

'For Each wks In ActiveWorkbook.Worksheets
Set rngTarget = wks.Range("H27:Q27")
For Each rng In rngTarget
Set rngTimeOff = Range(Cells(rng.Offset(2, 0).Address), Cells(rng.Offset(4, 0).Address))
If WorksheetFunction.Sum(rngTimeOff) = 7 Then
MsgBox "Time off is 7 for " & rngTimeOff.Address
End If
Next
'Next

End Sub