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