If Then error - Type Mismatch

[h0l1yw0od]

Hi,

I've trying to use a simple bit of code but everytime i try to run it i get a Type Mismatch error.

If Range("B20:B31") < 0 Then

I need it to look at the range and if there is a number in those cells then it throws up a messagebox. The message box part works fine, but this teeny bit of If Range... code just kicks up a stink every time.

What am i missing?

 

[Joe4]

You cannot perform on operation like that on a range of cells, you can only do one cell at a time. Use a loop to loop through your range, something like:

    Dim cell As Range
    For Each cell In Range("B20:B31")
        If cell.Value < 0 Then MsgBox "Conditions met"
    Next cell

 

[DominicB]

Good afternoon h0l1yw0od

A couple of issues. Do you want to check if the cells are empty or if the values of the cells are less than zero. The example below should should cover both requirements:

Sub test()
Range("B20:B31").Select
For Each Rng In Selection
'If Rng.Value < 0 Then
If IsEmpty(Rng.Value) = True Then
MsgBox Rng.Address
End If
Next Rng
End Sub

There are more efficient ways of doing this than looping through the cells, but this is probably the best way to go if you are learning VBA.

HTH

DominicB

 

[h0l1yw0od]

Oops

Thanks for the help guys.

I actually found an alternative to this myself (believe it or not!). I forgot i had a cell acting as a total for the range i was using so i simply told it to look at that one cell and if that was more than 0 then to bring up the message box.

I appreciate your advice though, thank you.