VBA Script using For Loop and If/Then statement

Question

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

I think you missed Else, try writing Else after this line GoTo ExitHere — Bilal
– Bilal, Commented Nov 8, 2019 at 16:39
@Bilal An "else" is only required if you have an "else" command to perform. If there's only one thing you need done, you can just have an "if" with no "else". — TylerH
– TylerH, Commented Nov 8, 2019 at 16:43
@TylerH yes I know that, but seeing OP's code I thought OP might want to have dual condition If true, show msg and exit otherwise do some stuff etc.. — Bilal
– Bilal, Commented Nov 8, 2019 at 17:51

BigBen · Accepted Answer · 2019-11-08 17:03:35Z

2

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.

edited Nov 8, 2019 at 17:03

answered Nov 8, 2019 at 16:41

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Script using For Loop and If/Then statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related