CODEWITHSUNDEEP
excelvba
Brainiac
Brainiac

Reputation: 85

If statement for a specific value in a range doesn't work properly?

this is my code:

    For Each cell In Range("A1:A13")
    If cell.Value = "Totals" Then

    Else
        Sheets(1).Range("A2:G4").Select
        Selection.Copy
        wb2.Activate
        Sheets(1).Range("A2").Select
        ActiveSheet.Paste
    End If
Next cell

I wanted it to do nothing after Then, but even if it finds the word 'Totals', it still runs the code after Else. What I want is that if the word 'totals' is there in that range, then do nothing.

Upvotes: 0

Views: 159

Answers (1)

Nacorid
Nacorid

Reputation: 793

Instead of doing your copy-pasting inside the loop and risking running it 13 times just set a flag once (if "Totals" is found) and run your code once after the loop like this:

Dim found as Boolean

For Each cell In Range("A1:A13")
    If cell.Value = "Totals" Then found = True
Next cell

If Not found then
    Sheets(1).Range("A2:G4").Select
    Selection.Copy
    wb2.Activate
    Sheets(1).Range("A2").Select
    ActiveSheet.Paste
End If

And please look at this answer to get rid of Select and Activate as it is error-prone.

Upvotes: 3

Related Questions