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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi,
Get a rid of exit for when the following comdition is true
Code:
If MyInput = MyCell Then
 'Get a rid of exit for here
Else

End if
 
Upvote 0
Hi

Replace

For Each MyCell In [B4:B18]

with

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


It works for me!!
 
Upvote 0
I have tried both options but it is still not working except for the first item in the range.
 
Upvote 0
try thiscode:
Code:
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
 
Upvote 0
@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.
 
Upvote 0
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?
 
Upvote 0
@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.
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
          
            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
 
Upvote 0
Is this what you need?
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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