for loop with If then question

[Steve]

Morning all.
I'm looking to set up an automatic password sheet protection tool and there
are only specific elements that I need protected on the worksheets in my
workbooks.
As such, my goal is to look for a value, in a single column, and if the
value exists in one cell, protect that row. All other rows will remain
unprotected.

As I think about this, it seems to me that I'd need a for loop to iterate
through the rows of that one column, and then use an IF statement to look for
a value.

Part of my struggle is that not all worksheets start at the same start row.
Nor do all worksheets end at the same row. Therefore, I need a variable to
delineate my start and end points. I've already learned that LastUsedRow does
not work well enough to use for this.

E.g.
for i = firstusedrow to lastusedrow step 1
if .cell() <> "" OR " " then
Activesheet.protect.........

Your helps are appreciated.

 

[ker_01]

Steve-

If you are only checking to see if a specific value is /present/ to lock the
row, there isn't much harm in letting it check the rows prior to your data-
it won't find the value and won't lock the row(s). That is a simpler solution
than coding for the start row for every sheet; it may take a few more
miliseconds to process, but you won't notice.

Here is some code to find the last used row- my apologies to whomever
originally posted this, I didn't keep that noted in the workbook I just
pulled it from.

Function lastRow(sh As Worksheet) 'Finds last cell with data in the last
used row.
On Error Resume Next
lastRow = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
On Error GoTo 0
End Function

That should give you a better number for lastrow on each sheet

So overall, something like (aircode)

Dim sht as worksheet

For each sht in Activeworkbook.sheets
ThisSheetsLastRow = lastrow(sht)
For i = 1 to ThisSheetsLastRow
'your code to check each row and lock or unlock it
Next
Next

 

[Mike H]

Steve,

I wasn't clear what the protection criteria is so this loops down column A
firstrow to last row and locks cell if it finds 'This" or "That"

Sub Lock_Em_Up()
Dim FirstRow As Long
Dim LastRow As Long

Set sht = Sheets("Sheet1")
sht.Unprotect Password:="MyPass"
sht.Cells.Locked = False

FirstRow = sht.Range("A1").End(xlDown).Row
LastRow = sht.Cells(Rows.Count, "A").End(xlUp).Row

For x = FirstRow To LastRow
If sht.Cells(x, 1).Value = "This" Or sht.Cells(x, 1).Value = "That" Then
sht.Rows(x).Locked = True
End If
Next
sht.Protect Password:="MyPass"
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ryan H]

This code will loop through all your worksheets. I assume the data you are
trying to find is in Col. A. With each worksheet it will unlock all cells
then lock the row that contains the value you specify. You will have to
change your password and data value in this code to fit your application.
Hope this helps! If so, let me know, click "YES" below.

Sub LockRows()

Dim wks As Worksheet
Dim LastRow As Long
Dim rw As Long

For Each wks In Worksheets
With wks
.Unprotect Password:="Your Password Here"
.Cells.Locked = False
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For rw = 2 To LastRow
If .Cells(rw, "A").Value = "Your Data" Then
.Rows(rw).Locked = True
End If
Next rw
.Protect Password:="Your Password Here"
End With
Next wks

End Sub

 

[Steve]

Hi Ryan,

I've made a modification to your code as follows.

If .Cells(rw, "A").value <> "" Or " " Then

It's calling a type mismatch error. I haven't changed anything else to your
code.

I don't see anything in the excel help file that tells me I cannot use OR in
this manner.

Thank you.

 

[Steve]

Hi Mike,
Protection criteria wasn't critical here. I did record macro already and got
the selection elements for the criteria. Thank you.

I just was lost on the looping elements for starting at one position, and
working through each row one row at a time.

 

[ker_01]

I believe you will have to use separate statements;

If .Cells(rw, "A").value <> "" Or .Cells(rw, "A").value <> " " Then

(of course this relies on you being in a 'with' statement, for the .cells)

HTH,
Keith

 

[Dave Peterson]

And in case there are " " or " " or any number of spaces...

if trim(.cells(rw,"A").value = "" then
...

 

[Dave Peterson]

Oops. Missed a closing paren.

if trim(.cells(rw,"A").value) = "" then

 

[Ryan H]

To use the OR operator in VBA you have to have two expressions on each side
of the operator. See the help section on OR in the VBE. So your line of
code would have to look like this:

If .Cells(rw, "A").Value <> "" Or .Cells(rw, "A").Value <> " " Then

But you really should do it this way. You really should use:

If Trim(.Cells(rw, "A").Value) <> "" Then

TRIM will automatically remove all spaces before and after a string, but
will not remove spaces inside a string. You can see the help section on the
TRIM function as well Hope this helps! If so, let me know, click "YES"
below.

 

[Steve]

Yup. This did it. Thank you.

As all of elements that I'll be looking for in this routine will be 5
characters, the trim should do it.