Is there something wrong with that code?

Asked

Viewed 143 times

0

Function Check()

Dim valorA, valorB, valorG, valor2 As String
Dim rng As Range

valorG = Cells(ActiveCell.Row, "G").Value
valor2 = Cells("2", ActiveCell.Column).Value

For Each rng In Range("A3:A321")

    Do While rng <> ""

        valorA = rng.Value

        valorB = rng.Offset(0, 1).Value

        If valorA = valorG Then

            If valorB = valor2 Then

                ActiveCell.Value = rng.Offset(0, 2).Value

            End If
        End If
    Loop
Next rng

End Function
vba excel-vba
  • 1

    Friend, I believe that the code itself is ok, but some comments... a loop inside the other is never recommended and in this case your ActiveCell will always have the last desired result, when the two conditions of your ifs are contemplated. Is that right? What is the mistake? If you post the spreadsheet and explain the idea, you might get a more approximate answer to help with the problem.

  • Change Function Check() for Sub Check(). For you are using ActiveCell.

  • Or if you want to create a Function use Application.Caller and not ActiveCell

  • Please read the Manual on how NOT to ask questions, How we should format questions and answers? and Be more specific in the question. For the title Há algo errado com esse código? does not help to understand the problem.

1 answer

1

Good morning, I see some mistakes:

Erro1: in the declaration of the variables valora, valorB and valorG are as variables of type Variant and only valor2 as STRING.

If Valora, Valorb and Valorg are string, which I’m 99% sure is your case, you should exchange your statement for:

Dim valorA as string, valorB as string, valorG as string, valor2 As String

Your code may even work with VARIANT variables, but they consume more memory and run the code slower.

Erro2: This procedure should be a Sub instead of a Function

Erro3: in the line "valor2 = Cells("2", Activecell.Column). Value", you should not put 2 in quotes, because the code will interpret it as string and again your code will run more slowly.

Erro4: Your double conditional loop can be replaced by:

If valorA = valorG and valorB = valor2 Then
    ActiveCell.Value = rng.Offset(0, 2).Value
End If

Which is a much simpler expression to understand and maintain.

There may be other errors, but you would need to know the goal of the macro to interpret if it is a mistake. I don’t particularly like working with the offset property. I prefer to do Cells(row+1, column) or Cells(row, column+1) because debugging is easier.

Browser other questions tagged vba excel-vba

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