If string value in array then ... vba

Question

If string value in array then ... vba

Navigation

#1 by (1 votes)

1

I am working with a macro and what I want to do is compare the values of a column against the values in an array and if it finds it delete the entire row, what is not the instruction to find the value, my code:

Dim i, c, last_row As Integer
Dim myarray As Variant
Dim Product As String

myarray = Array("J8031A#ACC", "J8031A#ACQ", "A2W80A")
last_row = wsMissingSCM2.Range("A22").End(xlDown).Row

For c = 22 To last_row

 Product = wsMissingSCM2.Cells(c, 4).Value

 **If IsArray(Product) Then** 'Tengo este codigo pero no me funciona

    Rows(c).EntireRow.Delete

 End If
Next c

array vba

asked by Priscila Estala 20.06.2016 в 23:17

source

1 answer

MySQLSyntaxErrorException: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1' Conditional ng-if cycle using http service on angularjs

score 1 · Accepted Answer

From this answer in SO , what you need is an auxiliary function that verifies if a value exists in an array:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

And, now, use the function to do what you need when you find the value:

' ...
For c = 22 to last_row
    Product = wsMissingSCM2.Cells(c,4).value
    If IsInArray(Product, myarray) then
        Rows(c).EntireRow.Delete
    End If
next c
' ...