[RESOLVED] Scan for Locked Cells

[Static]

I have code that scans for locked cells.. only problem is it says they are ALL locked.

I have range(A1:C13) as not locked.. and the worksheet is protected.
Now I can change values in that range but no where else...
but code returns ALL cells as locked??

whats wrong with this:

Sub ScanCell()
Dim WB As Workbook
Dim WS As Worksheet
Dim tWS As Worksheet
Set WB = ActiveWorkbook
Dim sRange_Col As String
Dim sRange_Row As Long
Dim tmp As String
Dim tmpR() As String
Dim lRow As Integer
Dim lCol As Integer
lRow = 2
lCol = 64
WB.Worksheets.Add Sheets(1)
Set tWS = ActiveSheet
tWS.Name = "LOCKED CELLS"
For Each WS In WB.Worksheets
    If WS.Name <> tWS.Name Then
        tWS.Range("A1") = "LOCKED CELLS"
        lRow = 2
        lCol = lCol + 1
        tmp = Replace(WS.UsedRange.Address, "$A$1:", "")
        tmpR = Split(tmp, "$")
        sRange_Col = tmpR(1)
        sRange_Row = tmpR(2)
        For col = 65 To Asc(sRange_Col)
            For Row = 1 To sRange_Row
                If Range(Chr(col) & Row).Locked Then
                     tWS.Range(Chr(lCol) & lRow) = WS.Name & "!" & Chr(col) & Row
                     lRow = lRow + 1
                End If
            Next
        Next
    End If
Next
End Sub

Thanks!!

[DKenny]

If your row number is greater than 9, then this line of code will alyways give you a row number of 1 , so you're not really checking your whole range

E.g Cell C$14; tmpR = C14; sRange_Row = 1

sRange_Row = tmpR(2)

[Static]

sorry, but you are incorrect

tmp = Replace(WS.UsedRange.Address, "$A$1:", "") 'Used Range is $A$1:$K$23
'tmp now = "$K$23"
        tmpR = Split(tmp, "$") 'Splits by $ 
        sRange_Col = tmpR(1) 'Now = K
        sRange_Row = tmpR(2)'Now = 23

the output also shows the entire range.. if I was not scanning the whole sheet.. the output would not show the entire sheet

[DKenny]

Doh - it is an array, not a variable - my bad.

[Static]

no problem..
You did make me catch something though.. I changed code now to scan ONLY the used range.. not A1 through Used Upper range...
new code:

Sub ScanCell()
Dim WB As Workbook
Dim WS As Worksheet
Dim tWS As Worksheet
Set WB = ActiveWorkbook
Dim sRange_Col1 As String
Dim sRange_Row1 As Long
Dim sRange_Col2 As String
Dim sRange_Row2 As Long
Dim tmp As String
Dim tmpR1() As String
Dim tmpR2() As String
Dim lRow As Integer
Dim lCol As Integer
lRow = 2
lCol = 64
WB.Worksheets.Add Sheets(1)
Set tWS = ActiveSheet
tWS.Name = "LOCKED CELLS"
For Each WS In WB.Worksheets
    If WS.Name <> tWS.Name Then
        tWS.Range("A1") = "LOCKED CELLS"
        lRow = 2
        lCol = lCol + 1
        tmp = WS.UsedRange.Address
        If InStr(tmp, ":") Then
            tmpR1 = Split(tmp, ":")
            tmpR2 = Split(tmpR1(1), "$")
            sRange_Col2 = tmpR2(1)
            sRange_Row2 = tmpR2(2)
            tmpR2 = Split(tmpR1(0), "$")
            sRange_Col1 = tmpR2(1)
            sRange_Row1 = tmpR2(2)
        Else
            tmpR1 = Split(tmp, "$")
            sRange_Col1 = tmpR1(1)
            sRange_Row1 = tmpR1(2)
            sRange_Col2 = tmpR1(1)
            sRange_Row2 = tmpR1(2)
        End If
        For col = Asc(sRange_Col1) To Asc(sRange_Col2)
            For Row = sRange_Row1 To sRange_Row2
                If Range(Chr(col) & Row).Locked Then 'HERE IS WHERE IT DOESNT SEEM TO WORK
                     tWS.Range(Chr(lCol) & lRow) = WS.Name & "!" & Chr(col) & Row
                     lRow = lRow + 1
                End If
            Next
        Next
    End If
Next
End Sub

[DKenny]

I think the problem is that when you are checking the cell to see if it is locked you are not refrencing the "WS" worksheet. Here's your code with a WITH statement added which ensures the .Range(Chr(col) & Row) refers to the correct sheet.

Hope this is right after my last post here....

Sub ScanCell()
Dim WB As Workbook
Dim WS As Worksheet
Dim tWS As Worksheet
Set WB = ActiveWorkbook
Dim sRange_Col As String
Dim sRange_Row As Long
Dim tmp As String
Dim tmpR() As String
Dim lRow As Integer
Dim lCol As Integer
lRow = 2
lCol = 64
WB.Worksheets.Add Sheets(1)
Set tWS = ActiveSheet
tWS.Name = "LOCKED CELLS"
For Each WS In WB.Worksheets
    With WS
        If Left(.Name, 4) <> "LOCK" Then
            tWS.Range("A1") = "LOCKED CELLS"
            lRow = 2
            lCol = lCol + 1
            tmp = Replace(.UsedRange.Address, "$A$1:", "")
            tmpR = Split(tmp, "$")
            sRange_Col = tmpR(1)
            sRange_Row = tmpR(2)
            For col = 65 To Asc(sRange_Col)
                For Row = 1 To sRange_Row
                    If .Range(Chr(col) & Row).Locked = True Then
                         tWS.Range(Chr(lCol) & lRow) = WS.Name & "!" & Chr(col) & Row
                         lRow = lRow + 1
                    End If
                Next
            Next
        End If
    End With
Next
End Sub

[Static]

you're right! good catch!
changed it to:

If WS.Range(Chr(col) & Row).Locked = True Then

and it worked perfectly

Thanks DKenny!