Issue with For Loops

Question

why does this not work? Basically i have a list in a single cell i want to split strings ending in "sec" and ones that don't by copying them into different columns.

Sub test_if()
   For i = 1 To 300
      Cells(i, 2).Select
      If Right(Cells(i, 2), 3) = "SEC" Then
         ActiveCell.Select
         Selection.Copy
         Cells(i, 3).Select
         ActiveSheet.Paste
      End If

      If Right(Cells(i, 2), 3) <> "SEC" Then
         ActiveCell.Select
         Selection.Copy
         'Cells(i, 4).Select
         ActiveCell.Offset(i - 1, 2).Select
         ActiveSheet.Paste
      End If
   Next i    
   Cells(1, 1).Select
End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

Try this one:

Sub test_if()
    Dim i As Integer

    For i = 1 To 300
        With Cells(i, 2)
            If UCase(Right(.Value, 3)) = "SEC" Then
               .Offset(, 1).Value = .Value
            Else
               .Offset(i - 1, 2).Value = .Value
            End If
        End With
   Next i
End Sub

and also read, please, how to avoid using Select/Active statements

edited May 23 '17 at 12:28

Community

1
1

answered Mar 05 '14 at 18:04

Dmitry Pavliv

35,333
13
79
80

Issue with For Loops

1 Answers1