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 is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,056
Messages
6,105,649
Members
447,974
Latest member
misspancake

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
Back
Top