For each loop is working for only first item in a range
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: For each loop is working for only first item in a range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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
    There Is Always A Better Way

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi

    Replace

    For Each MyCell In [B4:B18]

    with

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


    It works for me!!

  4. #4
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    I have tried both options but it is still not working except for the first item in the range.
    There Is Always A Better Way

  5. #5
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    827
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    1 Thread(s)

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

    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
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  6. #6
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    @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.
    There Is Always A Better Way

  7. #7
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

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

    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?
    Regards,
    Sebastian

    "When you've eliminated the impossible, whatever remains, however improbable, must be the truth."

  8. #8
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    @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
    There Is Always A Better Way

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    @Fluff,
    Yes this is exactly what I am looking for...
    Well appreciated
    There Is Always A Better Way

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •