CODEWITHSUNDEEP
excelvba
Bill Flippen
Bill Flippen

Reputation: 453

Doing if- then- next in Excel VBA

I have 3 columns that I am searching for text in. If the text appears in any one column I want to add a row and do some additional configuration.

However, if it appears more than once in a row I want it to stop and move to next row.

Logic Being: check row one column at a time, if ABC appears, insert row set counter=1 if counter =1 skip to next row.

For x = 1 To 1000
  Count = 0
  For y = 1 To 19
    If Count = 1 Then Next x
    End If
    If Left(cell(x, y), 8) = "ABC" Then
      Rows(x+1).Insert
      Count = 1
    End If
  Next y
Next x

Upvotes: 2

Views: 2873

Answers (1)

MatthewD
MatthewD

Reputation: 6761

Dim ws As Excel.Worksheet
Set ws = Application.ActiveSheet
Dim x As Integer
Dim y As Integer
Dim Count As Integer

'Loop the rows
For x = 1 To 1000
    Count = 0
    'Check the columns
    For y = 1 To 19
        If Left(ws.Cells(x, y), 3) = "ABC" Then
            'Increment the counter if we found it
            Count = Count + 1
            'This will prevent looping all the columns once we have more than one occurrence.  This will help performance. 
            If Count > 1 Then
                Exit For
            End If
        End If
    Next y
    'After checking all the columns, add a row if we found the text only one time
    If Count = 1 Then
        ws.Rows(x+1).Insert
        'Do other stuff here
    End If
Next x

Upvotes: 1

Related Questions