Loop in cells

[Gonzalex]

Hi,

i have a problem, i want to make a loop to a column in excel, and when a cell is empty e want to block it, i made the code and what appens is when in the cycle is found a value different from empty, from there to front the program don't do anything. example if the first three cells are empty, the 4rd is with a value and the 5th is empty again, the program block the first three and then from there to the end of the cycle don't do anything.

Here is the code that i'm using:

protection = Sheets("SEM-BPS 1").ProtectContents
If protection = True Then
ThisWorkbook.Sheets("SEM-BPS 1").Unprotect ("SAP")
End If

ThisWorkbook.Worksheets("SEM-BPS 1").Activate

For Each c In ThisWorkbook.Worksheets("SEM-BPS 1").Range("A7", Range("A7").End(xlToRight)).Cells
If c.Value = "Planeado 2007" Then
For Each j In ThisWorkbook.Worksheets("SEM-BPS 1").Range(c.Address, Range(c.Address).End(xlDown)).Cells
If j.Value = Empty Then
Worksheets("SEM-BPS 1").Range(j.Address).Locked = True
End If
Next j
End If
Next c

If protection = True Then
ThisWorkbook.Sheets("SEM-BPS 1").EnableOutlining = True
ThisWorkbook.Sheets("SEM-BPS 1").Protect Password:="SAP", _
contents:=True, _
userInterfaceOnly:=True, _
DrawingObjects:=True
End If

 

[Bong]

I think the problem may be that .End(xlDown) is going to stop at the first blank cell. You need to explore a little further down the column to see if there's more data.

_________________
Bob Rashkin

 

[SkipVought]

Hi,

Try something like this...

With ThisWorkbook.Worksheets("SEM-BPS 1")
    For Each c In .Range(.Cells(7, "A"), .Cells(.Cells.Rows.Count, "A").End(xlUp))
'.....

End With

Skip,

_[red][/red]