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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,306
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top