CODEWITHSUNDEEP
vbaexcelfor-loopoffset
user3384520
user3384520

Reputation: 1

Issue with For Loops

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

Upvotes: 0

Views: 36

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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

Upvotes: 1

Related Questions