Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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 "Steve" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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. "Steve" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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 -- Cheers, Ryan "Steve" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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. "Ryan H" wrote: 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 -- Cheers, Ryan "Steve" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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. "Mike H" wrote: 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. "Steve" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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 "Steve" wrote: 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. "Ryan H" wrote: 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 -- Cheers, Ryan "Steve" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
And in case there are " " or " " or any number of spaces...
if trim(.cells(rw,"A").value = "" then ... Steve wrote: 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. "Ryan H" wrote: 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 -- Cheers, Ryan "Steve" wrote: 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. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
Oops. Missed a closing paren.
if trim(.cells(rw,"A").value) = "" then Dave Peterson wrote: And in case there are " " or " " or any number of spaces... if trim(.cells(rw,"A").value = "" then ... Steve wrote: 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. "Ryan H" wrote: 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 -- Cheers, Ryan "Steve" wrote: 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. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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. -- Cheers, Ryan "Steve" wrote: 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. "Ryan H" wrote: 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 -- Cheers, Ryan "Steve" wrote: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with If then question
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. "Ryan H" wrote: 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. -- Cheers, Ryan "Steve" wrote: 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. "Ryan H" wrote: 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 -- Cheers, Ryan "Steve" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Do Until question | Excel Programming | |||
For Next Loop Question | Excel Programming | |||
end with loop question | Excel Programming | |||
do until...loop question | Excel Programming | |||
another loop question | Excel Programming |