If Statement for Range equal to Zero-HELP!!

[tatertot]

Hello All,


I am wanting to perform a simple macro, in my mind, of confirming if a set range of cells all = 0. My current macro is giving me errors. It is the following:

Sub ValidateCheckData()

Dim ValidationRange As Range
Set ValidationRange = Range("H23:I27")


If ValidationRange.Value <> 0 Then
    MsgBox "Your data does not balance.  Please review 'Input Project Data' tab."
Else
    MsgBox "Your Form IIIa and Upload Template align.  Please proceeed"
End If




End Sub

Please assist with this complication I am having with my little macro knowledge. I cannot find anything on Google.

 

[bhos123]

Sub ValidateCheckData()

Dim ValidationRange As Range
Set ValidationRange = Range("H23:I27")


If IsEmpty(ValidationRange.Value) = False Then
MsgBox "Your data does not balance. Please review 'Input Project Data' tab."
Else
MsgBox "Your Form IIIa and Upload Template align. Please proceeed"
End If


End Sub

 

[Tyler Hilton]

Hi Tatertot,

I think the reason you're seeing an error is due to the fact that your code is trying to find the value of a large range rather than the individual cells within that range. Try this:

Sub ValidateCheckData()

Dim ValidationRange As Range
[COLOR=#ff0000]Dim Cell as Range[/COLOR]
Set ValidationRange = Range("H23:I27")


[COLOR=#ff0000]For Each Cell in ValidationRange[/COLOR]
    If [COLOR=#ff0000]Cell[/COLOR].Value <> 0 Then
         MsgBox "Your data does not balance.  Please review 'Input Project Data' tab."
         [COLOR=#ff0000]Exit Sub[/COLOR]
    Else
         MsgBox "Your Form IIIa and Upload Template align.  Please proceed"
    End If
[COLOR=#ff0000]Next Cell[/COLOR]

End Sub

Hope this helps.

Best,

Tyler Hilton

 

[marcelocbd]

I think you need this:

Sub CheckCellEqualZero()

Dim ValidationRange  As Range
Dim c As Range
Set ValidationRange = Range("B3:B8")

For Each c In ValidationRange.Cells
    If c.Value = 0 Then
        MsgBox "Your Form IIIa and Upload Template align.  Please proceeed"
        GoTo Final
    End If
Next
MsgBox "Your data does not balance.  Please review 'Input Project Data' tab."
    
Final:
End Sub

I think the above code, posted bybhos123, will check if all values are ""(empty).
This one will check if all values are diffetent from 0.

 

[Kenneth Hobson]

The sum is not the full answer. Min and Max may be needed. If null values "" need to be checked, a loop to check each cell might be needed too or a Find.

Sub ValidateCheckData()
  Dim r As Range
  Set r = Range("H23:I27")
    If WorksheetFunction.Sum(r) <> 0 Then
        MsgBox "Your data does not balance.  Please review 'Input Project Data' tab."
    Else
        MsgBox "Your Form IIIa and Upload Template align.  Please proceeed"
    End If
End Sub

 

[tatertot]

Tyler, thanks so much for the revision to the code. Only issue is, I do not need the code to loop through each cell. I just simply need it to validate that all of the cells = 0 in one execution. If there is one that does not = 0 then state the messgae box that the data is not balanced and to review again. Can that be done?

 

[Jonmo1]

Try

If Application.Countif(ValidationRange,0) <> ValidationRange.Cells.Count Then

 

[tatertot]

Hey Ken,

Thanks so much for the code! Worked great! Appreciate all the time you put into this.

 

[Jonmo1]

You're welcome.