Excel - VBA - Search Array Value in Worksheet

Question

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?

Fratyx · Accepted Answer · 2015-11-17 12:58:13Z

1

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

answered Nov 17, 2015 at 12:58

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3364233 Over a year ago

Works a treat thanks. But can you explain it to me! It is like you are using a double negative to make a positive in the if statement to allow me to step through the statement if the array value is found. Rng is set to the value of my array, that is fine. But then ----- "If NOT Rng is NOTHING Then" Am I reading that right?

Fratyx Over a year ago

Nothing is a keyword indicating that an object has no value. As there is no keyword Anything you have to use the double negation.

Vincent G Over a year ago

Nothing is a special value in vba. Range.Find() returns that value 'Nothing' when it does not find a match, and that's what Fratyx is checking in is if statement.

Collectives™ on Stack Overflow

Excel - VBA - Search Array Value in Worksheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related