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

[kelly mort]

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?

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

 

[Mentor82]

Hi,
Get a rid of exit for when the following comdition is true

If MyInput = MyCell Then
 'Get a rid of exit for here
Else

End if

 

[HighAndWilder]

Hi

Replace

For Each MyCell In [B4:B18]

with

For Each MyCell In Range("B4:B18").Cells


It works for me!!

 

[kelly mort]

I have tried both options but it is still not working except for the first item in the range.

 

[offthelip]

try thiscode:

Sub test()
        MyInput = Application.InputBox("Enter the item name", "")
        If MyInput = False Or MyInput = "" Then Exit Sub
        MyInput = Replace(UCase(Trim(MyInput)), " ", "")
inarr = Range("B4:B18")
For i = 1 To 15
 MyCell = inarr(i, 1)
 If MyCell = MyInput Then
  MsgBox "My Input  is " & MyInput & " , MyCell is " & MyCell
  'exit for ' you can take the comment out of this line if you want to stop when a match is found
 Else
 MsgBox "My Input  is " & MyInput & " , MyCell is " & MyCell
 End If
Next i
End Sub

 

[kelly mort]

@offthelip,
Your code worked based on what I posted. I just figured out what I actually want to achieve is slightly different from the code I wrote.

So I observed that if the code compares my input to that of cell B4 and it does not match, then it jumps to the else statement. Which means that it has not the chance to compare with the remaining cells.

Is there a way I can loop through all the cells before shooting the else statement?

I am now adapting to these loops and it's a bit confusing most of the time.

 

[Mentor82]

Hi,
My recommendation is you right what you exactly expect from algorism to be done. How the code should work? Based on that I can offer you fixing/writing down the code adjusted to your needs.
In the code user type in item name, then your code gets a rid off spaces and changes the text to capitals letters. Up so far for me it's clear but what you do expect to happen next, pls describe it?

 

[kelly mort]

@Mentor82

What I want my code do now is that it should check all the cells for the user input before shooting the else condition.

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
          
            End If
        Next MyCell
    Wend

For example the above code works fine to locate the item. My want is display the alert after it has looked through all the cells and found no match with the user input.

When the else statement was there it jumps to the Else statement just after the first check is false.

I hope this is clear for you now. Let me know if any additional info is needed

 

[Fluff]

Is this what you need?

Sub kellymort()
   Dim Cl As Range
   Dim MyInput As Variant
   Dim Flg As Boolean
   
   MyInput = Application.InputBox("Enter the item name", "")
   If MyInput = False Or MyInput = "" Then Exit Sub
   MyInput = Replace(UCase(Trim(MyInput)), " ", "")
   For Each Cl In Range("B4:B18")
      If Cl.Value = MyInput Then
         Flg = True
         Exit For
      End If
   Next Cl
   If Not Flg Then MsgBox MyInput & " was not found"
End Sub

 

[kelly mort]

@Fluff,
Yes this is exactly what I am looking for...
Well appreciated