PDA

View Full Version : Solved: locked method of range failed



CatDaddy
05-21-2012, 11:01 AM
Sub replaceformulas()
Dim wb As Workbook
Dim lr As Long
For Each wb In Application.Workbooks
If wb.name <> ThisWorkbook.name Then
If Not InStr(wb.name, "May Meter") Then
wb.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row

replaceformulas2 lr
End If
End If
Next wb
End Sub
Private Sub replaceformulas2(lr As Long)

Range("AB2").Formula = "=IF((X2-V2)-U2>0,(X2-V2)-U2,0)"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & lr), Type:=xlFillDefault
Range("AD2").Formula = "=AB2*AC2"
Range("AD2").AutoFill Destination:=Range("AD2:AD" & lr), Type:=xlFillDefault
'Range("B2:G" & lr & ",AA2:AD" & lr & "AL2:AL" & lr).Locked = True
'ActiveSheet.Protect

End Sub

trying to cycle through 50 workbooks and replace the formulas and lock the ranges but the locked property is not working

CatDaddy
05-21-2012, 11:17 AM
Private Sub replaceformulas2(lr As Long)

Range("AB2").Formula = "=IF((X2-V2)-U2>0,(X2-V2)-U2,0)"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & lr), Type:=xlFillDefault
Range("AD2").Formula = "=AB2*AC2"
Range("AD2").AutoFill Destination:=Range("AD2:AD" & lr), Type:=xlFillDefault
With Sheets("Sheet1")
.Unprotect
.Cells.Locked = False
.Range("B2:G" & lr & ", AA2:AD" & lr & ", AL2:AL" & lr).Locked = True
.Protect Contents:=True
End With

End Sub