lundi 30 mai 2016

Find value in column and change cell to left with an if statment

This VBA script should take the value in the cell A37 and check if its in the C column of another worksheet. When the number is found the column to the left should be changed to 0. If it is already 0 then a message box will inform the user and if the number does not exist another message box will inform them of this.
This is the VBA I am using to accomplish this. However, every time I try to run it there is a "compile error: Next without For"

Sub Cancelled()

Dim x As Long
Dim regRange As Range
Dim fcell As Range
x = ThisWorkbook.Sheets("Welcome").Range("A37").Value
Set regRange = ThisWorkbook.Sheets("Registration").Range("C:C")
For Each fcell In regRange.Cells
    If fcell.Value = x Then
        ActiveCell.Offset(0, -1).Select
        If ActiveCell.Value = 1 Then
            ActiveCell.Value = 0
            MsgBox "Changed to zero"
            Exit Sub
        Else
            MsgBox "That registration number is already cancelled"
            Exit Sub
    End If
Next fcell
    MsgBox "That number does not exist"

End Sub

Aucun commentaire:

Enregistrer un commentaire