How to use foreach in my current macro

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
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 ?


VBA Code:
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


Capturar.JPG
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

severynm

Active Member
Joined
Jan 8, 2021
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
    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

Well-known Member
Joined
Apr 5, 2021
Messages
1,769
Office Version
  1. 2010
Platform
  1. Windows
As a loop is useless, just using directly some Excel basics like MATCH or VLOOKUP worksheet function …​
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,198
Members
425,459
Latest member
Danniey

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
Top