excelvbaif-statementoffsetinstr

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


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.

analysing


Solution

  • 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