vba doing something I dont want it to do, using if else

Question

I have this piece of code that analyses the values on Column J [Valor].
If the code is negative, it checks the Column K [Descrição] if there is a specific Array of text.
If the text exists, it does a few changes, if not, a few other changes.
But, one of the changes is acting on another.

Sub valor_neg()

Dim valor_b As Range
Set valor_b = Range(Range("J2"), Range("J2").End(xlDown))

Dim valor_2 As Integer
Dim valor_f As Range
Dim descricao() As Variant
descricao = Array("Caução", "As Built", "Asbuilt", "As-built", "Garantia", "Aceite")
For Each valor_f In valor_b
valor_2 = 0
    If valor_f.Value < 0 Then
        For valor_2 = LBound(descricao) To UBound(descricao)
            If InStr(1, valor_f.Offset(0, 1).Value, descricao(valor_2), vbTextCompare) > 0 Then
                valor_f.Offset(0, -7).Value = descricao(valor_2)
                valor_f.Offset(0, -1).Value = "31/12/" & Year(Date) + 1

            Else
                valor_f.Offset(0, -9).Value = "Tesouraria"
            End If
        valor_f.Offset(0, 2).Value = "NA"
        valor_f.Offset(0, 3).Value = "NA"
        Next valor_2
    End If
Next valor_f

End Sub

Can you help figure it out, correctly. Maybe the code is not perfect.
The way I planned, where I highlighted green, is correct, where is yellow, incorrect.
See, where the code finds the Array, it changes the column I [Vencimento] correctly (it does not change to 31/12/2023 when the Array is not there), but it should not change column A [Lançamento] at all, this action is on the Else part.

You run the `If..Else` for every value of `valor_2`, so that test will execute multiple times as you loop over the array. — Tim Williams, Jul 22 '22 at 22:56

score 2 · Accepted Answer · answered Jul 22 '22 at 23:10

The logic is easier if you push the "find match in array" out into a separate function:

Sub valor_neg()

    Dim valor_b As Range, ws As Worksheet
    Dim valor_f As Range
    Dim descricao() As Variant, m As String
    
    Set ws = ActiveSheet
    Set valor_b = ws.Range("J2", ws.Range("J2").End(xlDown))
    
    descricao = Array("Caução", "As Built", "Asbuilt", "As-built", "Garantia", "Aceite")
    
    For Each valor_f In valor_b
        If valor_f.Value < 0 Then
            
            m = ArrayMatch(descricao, valor_f.Offset(0, 1).Value) 
            If Len(m) > 0 Then    'any match?
                valor_f.Offset(0, -7).Value = m
                valor_f.Offset(0, -1).Value = "31/12/" & Year(Date) + 1
            Else
                valor_f.Offset(0, -9).Value = "Tesouraria" 'no match found
            End If
            
            valor_f.Offset(0, 2).Value = "NA"
            valor_f.Offset(0, 3).Value = "NA"
           
        End If 'value>0
    Next valor_f

End Sub

'Find first element in array `arr` equal to or substring of value `v`
Function ArrayMatch(arr, v) As String
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        If InStr(1, v, arr(i), vbTextCompare) > 0 Then
            ArrayMatch = arr(i)
            Exit Function
        End If
    Next i
End Function

it worked perfectly. Thanks a lot. The code ended up not very crazy to undestand! ♥ — Afonso Medeiros, Jul 25 '22 at 22:27

score 0 · Answer 2 · answered Jul 22 '22 at 22:44

Was it in wrong order?

Sub valor_neg()

Dim valor_b As Range
Set valor_b = Range(Range("J2"), Range("J2").End(xlDown))

Dim valor_2 As Integer
Dim valor_f As Range
Dim descricao() As Variant
descricao = Array("Caução", "As Built", "Asbuilt", "As-built", "Garantia", "Aceite")
For Each valor_f In valor_b
valor_2 = 0
    If valor_f.Value < 0 Then
        For valor_2 = LBound(descricao) To UBound(descricao)
            If InStr(1, valor_f.Offset(0, 1).Value, descricao(valor_2), vbTextCompare) > 0 Then
                valor_f.Offset(0, -7).Value = descricao(valor_2)
                valor_f.Offset(0, -1).Value = "31/12/" & Year(Date) + 1
            End If
            valor_f.Offset(0, 2).Value = "NA"
            valor_f.Offset(0, 3).Value = "NA"
        Next valor_2
    Else
        valor_f.Offset(0, -9).Value = "Tesouraria"
    End If
Next valor_f
End Sub

It still didnt work properly :( – Afonso Medeiros Jul 25 '22 at 21:33 — Afonso Medeiros, Jul 25 '22 at 21:33

vba doing something I dont want it to do, using if else

2 Answers2