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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,751
Office Version
2016
Platform
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
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
 

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
Hi

Replace

For Each MyCell In [B4:B18]

with

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


It works for me!!
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,751
Office Version
2016
Platform
Windows
I have tried both options but it is still not working except for the first item in the range.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,266
Office Version
2010
Platform
Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,751
Office Version
2016
Platform
Windows
@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

Active Member
Joined
Dec 30, 2018
Messages
307
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,751
Office Version
2016
Platform
Windows
@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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,552
Office Version
365
Platform
Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,751
Office Version
2016
Platform
Windows
@Fluff,
Yes this is exactly what I am looking for...
Well appreciated
 

Watch MrExcel Video

Forum statistics

Threads
1,102,194
Messages
5,485,299
Members
407,494
Latest member
RachelBuckland

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top