CODEWITHSUNDEEP
excelvbaif-statement
EugeneC83
EugeneC83

Reputation: 5

VBA Script using For Loop and If/Then statement

I'm still new to VBA and currently writing a code to try and check whether a column has a specific value in each row. It requires the use of a for loop and an if/then statement.

I wrote the script but when I execute it, it does nothing. Can someone help me check if my code is written correctly?

Sub TestCheck()

    Dim Rng As Range
    Dim xlsheet As Object
    Dim c As Range
    Dim LastRow As Long

    'Checks each cell value in column A for string - "Iris Concept of Operations"'
    n = 0

    Set xlsheet = ActiveSheet
    With xlsheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    If .Cells(.Rows.Count, "A").End(xlUp).Row = 0 Then
        MsgBox "The spreadsheet doesn't work"
        GoTo ExitHere

        Set Rng = .Range(LastRow).Offset(n, 0)

        For Each c In Rng
            n = n + 1

            If InStr(c.Value, "Iris Concept of Operations") > 0 Then
                With xlsheet
                    .Range("A1").Offset(n, 8) = c.ClearContents
                End With
            Else
                With xlsheet
                    .Range("A1").Offset(n, 2) = c.ClearContents
                End With
            End If
        Next c

    End If

    ExitHere:
        Set xlsheet = Nothing
        Set Rng = Nothing
    End With

End Sub

Upvotes: 0

Views: 75

Answers (1)

BigBen
BigBen

Reputation: 50008

The End If is in the wrong spot. Properly indenting your code would show this. Right now your loop is all inside the If...End If, and you exit before hitting the loop.

If .Cells(.Rows.Count, "A").End(xlUp).Row = 0 Then
    MsgBox "The spreadsheet doesn't work"
    GoTo ExitHere
End If  ' Move the End If here.

Also, there is no row 0. Your condition can never be true.

.Cells(.Rows.Count, "A").End(xlUp).Row = 0

Also, this is problematic:

Set Rng = .Range(LastRow).Offset(n, 0)

LastRow is not a valid Range reference. Also, if you Offset from one cell, the result is still one cell, so you won't be looping through multiple cells.

And this is also problematic:

.Range("A1").Offset(n, 8) = c.ClearContents

ClearContents doesn't return anything and should not be in a line with =.

In short, I'd suggest that you break this down into smaller parts that you can get working on their own.

Upvotes: 2

Related Questions