How to locate next values of the same customer

Asked

Viewed 433 times

1

If you could just help me out with this one, and I think you’ll be good for someone else, too. I am trying to make sure that by clicking search the Textbox are filled with customer data, and clicking again on the Search button the Textbox is loaded with the data of the next record of the same client. That is, it goes from registration to registration of the same code.

I’m having a hard time on the Loop.

Private Sub cmdPequisar_Click()
'Verificar se foi digitado um nome na primeira caixa de texto
If txtCPF.Text = "" Then
MsgBox "Digite o CPF de um cliente"
txtCPF.SetFocus
GoTo Linha1
End If

With Worksheets("Dados Clientes").Range("A:A")
Set c = .Find(txtCPF.Value, LookIn:=xlValues, LookAt:=xlPart)

Do Until Sheets("Dados Clientes").Cells(0, 1) = ""
If Sheets("Dados Clientes").Cells(0, 1) = txtCPF Then

If Not c Is Nothing Then

c.Activate
txtCPF.Value = c.Value
txtNome.Value = c.Offset(0, 1).Value
txtEndereco.Value = c.Offset(0, 2).Value
cboEstado.Value = c.Offset(0, 3).Value
cboCidade.Value = c.Offset(0, 4).Value
txtTelefone.Value = c.Offset(0, 5).Value
txtEmail.Value = c.Offset(0, 6).Value
txtNascimento.Value = c.Offset(0, 7).Value
Exit Sub

   Linha = Linha + 1

Loop

Else
MsgBox "Cliente não encontrado!"
End If
End With
Linha1:

End If
End Sub
excel vba
  • 1

    Thank you very much Guilherme Lima, I am very new not noticed when I copied the code.

2 answers

1


@ludehenrique2cia, note that there are instructions that will not be accessed after the "Exit Sub", as in the excerpt copied below:

Exit Sub

Row = Row + 1

...

The "Exit Sub" leaves the routine at this point, so the instructions that are after it in the natural sequence of this part of the code will not be executed (in the above case, Linha = Linha + 1 will never be executed).

  • Thanks Leo, I made the change but continues the message "Compilation error" (Loop without Do). I don’t understand why I already put Do Until

  • 1

    Okay, but there’s another problem, please mark this answer that served you and create another question with the new problem.

  • 1

    Lude, if this answer served you, it is customary to mark it, there is no description of the problem you reported later on in the original question, so each question should be distinct. It is also customary to vote on the questions and answers, including the answers we give, I did not understand why to uncheck this answer whether it served you or was useful in any way.

  • I just fixed it, it was helpful but didn’t solve it. It’s so others can help too. Thanks!

  • 1

    Lude, please reschedule this too friend, grateful.

0

Try this way: 

Private Sub cmdPequisar_Click()

    'Verificar se foi digitado um nome na primeira caixa de texto
    If txtcpf.Text = "" Then
        MsgBox "Digite o CPF de um cliente"
        txtcpf.SetFocus

        Exit Sub

    End If

    With Worksheets("Dados Clientes").Range("A:A")

        Set c = .Find(txtcpf.Value, LookIn:=xlValues, LookAt:=xlPart)

        Do Until Sheets("Dados Clientes").Cells(0, 1) = ""

            If Sheets("Dados Clientes").Cells(0, 1) = txtcpf Then

                If Not c Is Nothing Then

                    c.Activate
                    txtcpf.Value = c.Value
                    txtNome.Value = c.Offset(0, 1).Value
                    txtEndereco.Value = c.Offset(0, 2).Value
                    cboEstado.Value = c.Offset(0, 3).Value
                    cboCidade.Value = c.Offset(0, 4).Value
                    txtTelefone.Value = c.Offset(0, 5).Value
                    txtEmail.Value = c.Offset(0, 6).Value
                    txtNascimento.Value = c.Offset(0, 7).Value

                    Exit Do

                    Linha = Linha + 1
                End If
            End If

        Loop

    End With

    If Trim$(txtcpf.Value) = "" Then
        MsgBox "Cliente não encontrado!"
    End If

End Sub

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.