For each loop is working for only first item in a range

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hi all,
I have this code which used to verify a value from a range of cells and that of my input. It used to work until yesterday when I did a few updates. I get the else alert for any item I feed the input box, except I feed it with the first item in the range – that is cell B4, where I get the match. Can someone tell me what I am doing wrongly?


Code:
Private MyCell As Range
Private MyInput  As Variant, result&


While result = 10
        MyInput = Application.InputBox("Enter the item name", "")
        If MyInput = False Or MyInput = "" Then Exit Sub
        MyInput = Replace(UCase(Trim(MyInput)), " ", "")
        For Each MyCell In [B4:B18]
            If MyInput = MyCell Then
    MsgBox "My Input  is " & MyInput & " , MyCell is " & MyCell
                result = 0
                Exit For
            Else
    MsgBox "My Input  is " & MyInput & " , MyCell is " & MyCell
                result = 10
                Exit For
            End If
        Next MyCell
    Wend


Thanks in advance
 
Glad we could help & thanks for the feedback
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
@Kelly_Mort: now your need is clear for me. I've noticed that fluff already helped you with the code which sounds good. I'm happy for you and glad we could help you. Thans for a feedback.
 
Upvote 0
@Kelly_Mort: now your need is clear for me. I've noticed that fluff already helped you with the code which sounds good. I'm happy for you and glad we could help you. Thans for a feedback.

Sure.
I am really having fun being here.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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