If Not statement with Message Box

lp3eb

New Member
Joined
Mar 22, 2013
Messages
11
Hello,

I have code written that allows the user to type a value into cell B5. If the value of B5 is equal to a value in column F in Sheet1, the value in cell B4 then becomes equivilant to the cell one column to the right of the 'anchor' cell in Database, column F.

I would like a message box to pop up if the entered value in cell B5 is NOT equal to any value in column F. For some reason the way I have my code written, the message box appears whether the value is equivilant or not. I think it has something to do with the "If Not ... " statement at the end of my code, but I don't know any way around it. Is there a way to get the message box to appear only if the value does not exist in sheet1 column F? I would appreciate any guidance.

My code is as follows:

Sub test()
Application.ScreenUpdating = False
Dim rng As Range
For Each rng In Sheet1.Range("F4:F1000")
If rng.Value = Range("B5") Then
Range("B4") = rng.Offset(0, 1)
End If

If Not Range("B5") = rng.Value Then
MsgBox "Error"
Exit Sub
End If
Next
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Add the following code to your worksheet change event (I wrote this assuming you were using sheet1, but if you are using another sheet, just change sheet1 to the appropriate sheet...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
If Target.Address = "$B$5" Then
    Set Rng = Sheet1.Range("F:F").Find(Target.Value, , , xlWhole)
    If Rng Is Nothing Then
        MsgBox ("Error")
    End If
End If

End Sub
 
Last edited:
Upvote 0
In your own code the next was in the wrong place,

It should have come after the first end if.
 
Upvote 0
Hi,

Try on a copy of sheet.

Code:
Sub Test()

Dim rrange As Range
Dim rcell As Range

Set rrange = Range("F4:F1000")

If Not Len(Range("B5")) = 0 Then

For Each rcell In rrange
    If rcell.Value = Range("B5").Value Then
        Range("B4") = rcell.Offset(0, 1)
    End If
Next rcell

If Len(Range("B4")) = 0 Then MsgBox "Error"

End If

End Sub

Jai
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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