CODEWITHSUNDEEP
vbaexcel
user3364233
user3364233

Reputation: 105

Excel - VBA - Search Array Value in Worksheet

I have a VBA macro, part of which loops through an array of defined strings and finds them in column A of the worksheet.

This works perfectly fine if all of the strings of the array exist in column A, but falls over if the string does not exist.

I have struggled with a simple if statement that would help me out. If array value is not found, move next 

For iArow = 0 To UBound(MyArray) - 1
    iRow = Range("A:A").Find(MyArray(iArow), LookIn:=xlValues, lookat:=xlWhole).Row
    Range("D" & iRow).ClearContents
    Range("I" & iRow).ClearContents
    Rows(iRow + 1).Insert
    Rows(iRow).Insert 
Next iArow

Any suggestions?

Upvotes: 0

Views: 925

Answers (1)

Fratyx
Fratyx

Reputation: 5797

Dows that help?

For iArow = 0 To UBound(myArray) - 1
    Set Rng = Range("A:A").Find(myArray(iArow), LookIn:=xlValues, lookat:=xlWhole)
    If Not Rng Is Nothing Then
        iRow = Rng.Row
        Range("D" & iRow).ClearContents
        Range("I" & iRow).ClearContents
        Rows(iRow + 1).Insert
        Rows(iRow).Insert
    End If
Next iArow

Upvotes: 1

Related Questions