How to use foreach in my current macro

[KhallP]

I created a macro to check if the user name and password are correct when he tries to log in, the code works perfectly but I would like to change it and start using it with the "for each" function, is this possible ?

Private Sub bt_seguinte_Click()

lb_erro_usuario.Visible = False
lb_erro_senha.Visible = False

Select Case True
    
Case Trim(tb_usuario) = "" And Trim(tb_senha) = ""
    lb_erro_usuario.Visible = True
    lb_erro_senha.Visible = True
    
Case Trim(tb_usuario) = ""
    lb_erro_usuario.Visible = True
    
Case Trim(tb_senha) = ""
      lb_erro_senha.Visible = True

Case Else

nome_usuario = tb_usuario.Value
senha = tb_senha.Value

Application.ScreenUpdating = False

Workbooks(OtherBookPath).Sheets("Usuários").Activate

contar_usuarios = Cells(Rows.Count, "D").End(xlUp).Row

For c = 2 To contar_usuarios

    Cells(c, 4).Select
    
    Select Case True
    
    Case ActiveCell = nome_usuario
        If ActiveCell.Offset(0, 1) = senha Then
            Workbooks(CurrentBookPath).Activate
            Unload Me
            Library_Choose.Show

        Else
            lb_erro_senha.Visible = True
            lb_erro_senha.Caption = "*Senha ou Nome de Usuário incorretos"
            
        End If
        
    Case c = contar_usuarios And ActiveCell <> nome_usuario
        lb_erro_senha.Visible = True
        lb_erro_senha.Caption = "*Senha ou Nome de Usuário incorretos"
              
    Case ActiveCell <> nome_usuario
    End Select

Next c

Workbooks(CurrentBookPath).Activate
Application.ScreenUpdating = True

End Select
End Sub

 

[severynm]

Yep it's pretty straight forward. You need to set one range object representing the range to iterate over and a second range object to represent each cell in the first range.

    Dim cell As Range
    Dim rng As Range
    Set rng = SheetName.Range("E2:E10")
    
    For Each cell In rng
        MsgBox cell.Value
    Next cell

With that said however, I am not understanding why would you need to?

 

[Marc L]

​

As a loop is useless, just using directly some Excel basics like MATCH or VLOOKUP worksheet function …​