Is there anything wrong with this code?

Question

Is there anything wrong with this code?

Navigation

#1 by (1 votes)

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

asked by anonymous 09.04.2018 / 02:33

1 answer

Ternary Conditional Operator Turning rows into columns in SQL [duplicate]

score 1 · Answer 1

Good morning, I see some errors:

Error1: In the declaration of the variables A, B, and G are as variables of type Variant and only value2 as STRING.

If ValueA, ValueB and ValueG are string, that I have 99% sure is your case, you should change your declaration by:

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 more slowly.

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

Error3: In the line "value2 = Cells (" 2 ", ActiveCell.Column) .Value", you should not put quotation marks around 2, because the code will interpret it as string and again your code will run more slowly. / p>

Error4: Your double conditional loop can be replaced by:

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

What is a much simpler expression to understand and to maintain.

There may be other errors, but you would need to know the purpose of the macro to interpret if it is an error. I particularly do not like working with the offset property. I prefer to make Cells (row + 1, column) or Cells (row, column + 1) as the debug becomes easier.