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