0

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Brainiac
  • 75
  • 10
  • Does your range contain any cells that don't show `"Totals"`? You're running your `If` statement for every single cell in your range, so I'm guessing that at least one `cell.Value` isn't `"Totals"` therefore your `Else` block is running. You should iterate to see if any single cell has `"Totals"` then, after checking all the cells in the range, decide if you're going to run your copy + paste. You could also use `Find` to do this without looping. – Michael Murphy Apr 16 '19 at 12:45
  • 2
    Your code checks 13 times `If cell.Value = "Totals"`, assuming only one value contains "Totals" your `Else` part of the if statement is run 12 times. – Nacorid Apr 16 '19 at 12:47

1 Answers1

3

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.

Nacorid
  • 783
  • 8
  • 21