If Then error - Type Mismatch

h0l1yw0od

New Member
Joined
Oct 6, 2005
Messages
21
Hi,

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

Code:
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,381
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
    Dim cell As Range
    For Each cell In Range("B20:B31")
        If cell.Value < 0 Then MsgBox "Conditions met"
    Next cell
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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

New Member
Joined
Oct 6, 2005
Messages
21
Oops

Thanks for the help guys.

I actually found an alternative to this myself :oops: (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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,128
Members
412,305
Latest member
Mozz
Top