RE: $$Excel-Macros$$ For Each loop
Hi Richard, Here's my approach: Sub prototype() Const ColumnlarCriteria As String = X3:X10005 Dim LockRange As Range Dim LockRow() As Variant Dim Row As Long With Sheet1 .Protect userinterfaceonly:=True Set LockRange = .Range(Y3:AX1000) LockRow() = WorksheetFunction.Transpose(.Evaluate(ColumnlarCriteria)) For Row = 1 To LockRange.Rows.Count LockRange.Rows(Row).Locked = LockRow(Row) Next Row End With End Sub You can add logic to determine the used rows if you don't already know what they are. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: Tuesday, June 26, 2012 8:37 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ For Each loop Thanks for your reply, but I guess I wasn't clear -- condition a ===if the value of cell x3=5 then I want cells y3 through ax3 to be unprotected, otherwise I want them to be protected. condition b ===if the value of cell x35 then the reverse happens and I would to do it for each row with data, x4=7 then condition b is met x5=5 then condition a is met x6=8 then condition b is met and so forth through probably hundreds of rows On Tuesday, June 26, 2012 8:26:13 AM UTC-7, Rajan_Verma wrote: No need to Loop UsedRange.Replace X, Set rng=UsedRange.SpecialCells(xlcelltypeblanks) Rng.value=X Rng.entireColumn.Locked=True/False (whatever your want) Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: 26 June 2012 8:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ For Each loop This one always confuses me, but it is usually simple, so I would appreciate any help. I want to loop through all the rows with data in my worksheet and if the value in a certain cell is equal to x then I want to unprotect selected columns in that row, otherwise I want to reset the protection in those columns in that row. Thank you in advance for any help Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
RE: $$Excel-Macros$$ For Each loop
You also might want to consider using a worksheet.change event to modify the locked status on a given row range when and only when values in column X are changed. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Saturday, June 30, 2012 6:24 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ For Each loop Hi Richard, Here's my approach: Sub prototype() Const ColumnlarCriteria As String = X3:X10005 Dim LockRange As Range Dim LockRow() As Variant Dim Row As Long With Sheet1 .Protect userinterfaceonly:=True Set LockRange = .Range(Y3:AX1000) LockRow() = WorksheetFunction.Transpose(.Evaluate(ColumnlarCriteria)) For Row = 1 To LockRange.Rows.Count LockRange.Rows(Row).Locked = LockRow(Row) Next Row End With End Sub You can add logic to determine the used rows if you don't already know what they are. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: Tuesday, June 26, 2012 8:37 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ For Each loop Thanks for your reply, but I guess I wasn't clear -- condition a ===if the value of cell x3=5 then I want cells y3 through ax3 to be unprotected, otherwise I want them to be protected. condition b ===if the value of cell x35 then the reverse happens and I would to do it for each row with data, x4=7 then condition b is met x5=5 then condition a is met x6=8 then condition b is met and so forth through probably hundreds of rows On Tuesday, June 26, 2012 8:26:13 AM UTC-7, Rajan_Verma wrote: No need to Loop UsedRange.Replace X, Set rng=UsedRange.SpecialCells(xlcelltypeblanks) Rng.value=X Rng.entireColumn.Locked=True/False (whatever your want) Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: 26 June 2012 8:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ For Each loop This one always confuses me, but it is usually simple, so I would appreciate any help. I want to loop through all the rows with data in my worksheet and if the value in a certain cell is equal to x then I want to unprotect selected columns in that row, otherwise I want to reset the protection in those columns in that row. Thank you in advance for any help Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal
RE: $$Excel-Macros$$ For Each loop
No need to Loop UsedRange.Replace X, Set rng=UsedRange.SpecialCells(xlcelltypeblanks) Rng.value=X Rng.entireColumn.Locked=True/False (whatever your want) Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: 26 June 2012 8:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ For Each loop This one always confuses me, but it is usually simple, so I would appreciate any help. I want to loop through all the rows with data in my worksheet and if the value in a certain cell is equal to x then I want to unprotect selected columns in that row, otherwise I want to reset the protection in those columns in that row. Thank you in advance for any help Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ For Each loop
Thanks for your reply, but I guess I wasn't clear -- condition a ===if the value of cell x3=5 then I want cells y3 through ax3 to be unprotected, otherwise I want them to be protected. condition b ===if the value of cell x35 then the reverse happens and I would to do it for each row with data, x4=7 then condition b is met x5=5 then condition a is met x6=8 then condition b is met and so forth through probably hundreds of rows On Tuesday, June 26, 2012 8:26:13 AM UTC-7, Rajan_Verma wrote: No need to Loop UsedRange.Replace “X”,”” Set rng=UsedRange.SpecialCells(xlcelltypeblanks) Rng.value=”X” Rng.entireColumn.Locked=True/False (whatever your want) * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Richard *Sent:* 26 June 2012 8:54 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ For Each loop This one always confuses me, but it is usually simple, so I would appreciate any help. I want to loop through all the rows with data in my worksheet and if the value in a certain cell is equal to x then I want to unprotect selected columns in that row, otherwise I want to reset the protection in those columns in that row. Thank you in advance for any help Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ For Each loop
To: excel-macros@googlegroups.com Sent: Tue, June 26, 2012 11:26:13 AM Subject: RE: $$Excel-Macros$$ For Each loop No need to Loop UsedRange.Replace “X”,”” Set rng=UsedRange.SpecialCells(xlcelltypeblanks) Rng.value=”X” Rng.entireColumn.Locked=True/False (whatever your want) Regards Rajan verma +91 7838100659 [IM-Gtalk] From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: 26 June 2012 8:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ For Each loop This one always confuses me, but it is usually simple, so I would appreciate any help. I want to loop through all the rows with data in my worksheet and if the value in a certain cell is equal to x then I want to unprotect selected columns in that row, otherwise I want to reset the protection in those columns in that row. Thank you in advance for any help Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
RE: $$Excel-Macros$$ For Each loop
Revised with Error Handling : Sub Mtest() Dim rngRange As Range Dim rngBlanks As Range Dim WksSheet As Worksheet Dim ValtoReplace Set WksSheet = ActiveSheet WksSheet.Unprotect Set rngRange = Range(rngRange).CurrentRegion On Error Resume Next Set rngBlanks = rngRange.SpecialCells(xlCellTypeBlanks) rngBlanks.Value = @@ On Error GoTo -1 rngRange.Locked = True ValtoReplace = Range(valToReplace).Value rngRange.Replace ValtoReplace, , , , True Set rngRange = rngRange.SpecialCells(xlCellTypeBlanks) rngRange.Value = ValtoReplace rngRange.EntireColumn.Locked = False rngBlanks.ClearContents WksSheet.Protect End Sub Regards Rajan verma +91 7838100659 [IM-Gtalk] From: Rajan_Verma [mailto:rajanverma1...@gmail.com] Sent: 26 June 2012 10:43 To: 'excel-macros@googlegroups.com' Subject: RE: $$Excel-Macros$$ For Each loop Well, This was the approach . See the attached file Sub Mtest() Dim rngRange As Range Dim rngBlanks As Range Dim ValtoReplace Set rngRange = Range(rngRange).CurrentRegion Set rngBlanks = rngRange.SpecialCells(xlCellTypeBlanks) rngBlanks.Value = @@ rngRange.Locked = True ValtoReplace = Range(valToReplace).Value rngRange.Replace ValtoReplace, , , , True Set rngRange = rngRange.SpecialCells(xlCellTypeBlanks) rngRange.Value = ValtoReplace rngRange.EntireColumn.Locked = False rngBlanks.ClearContents Worksheets(rngRange.Parent.Name).Protect End Sub So the Columns is containing the X values will be unlocked . I thinks its avoiding the loop and efficient way to accomplish this task. Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Paul Schreiner Sent: 26 June 2012 9:56 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ For Each loop The problem with that approach is that you're assuming that the cells in the UsedRange that do not have an X in them have some other value. If, for example, all of column A is blank, and the user places an X in column A for each row in which he wants to unlock specific cells in that row, this code would first,remove the X's, then unlock all rows! So, I think we need to go back to the original question... with regard to looping. There are several different kinds of looping methods. the decision on which one to use will primarily be influenced by the specific situation. In this case, Rich, you want to loop through all of your rows, looking for an X in a specific column. The simplest form a the loop is: For RowNum = FirstRow to LastRow ... Next RowNum The trick here is to figure out what your FirstRow and LastRow is. There are seveal ways to do this. Now, if you currenlty have 5,000 rows and you know you'll NEVER have more than 5,000 rows, then you can simply loop from RowNum = 1 to 5000. I the number of rows changes, you could ALWAYS just enter a very large number (64,000 for Excel2003, 1,000,000 for 2007-2010) But this is a waste of cpu cycles, since it will be processing a large number of blank rows. Now, many people like to use the technique where you select a cell near the bottom of the sheet, in a column that ALWAYS has a value, then use the Ctrl-UpArrow technique to find the last cell in that colum that has a value. Like: Range(B64000).Select Selection.End(xlUp).Select I personally don't like this technique because it relies on Selecting the sheet, and selecting cells on the sheet. Any graphical/display interaction takes up a significant amount of CPU time. (it's very inefficient) Instead, I like to combine a couple of other techniques. For instance: using Ctrl-End will take you to the cell in the furthest row/column that has data. Recording a macro to do this results in: ActiveCell.SpecialCells(xlLastCell).Select Now, as I said, I don't like to select cells (unless I have to) So, instead I use the .Row or .Column method to determine this last cell: LastRow = ActiveCell.SpecialCells(xlLastCell).Row LastCol = ActiveCell.SpecialCells(xlLastCell).Column This still requires that a cell be selected (like: Range(A1).select) But it doesn't jump around the sheet. Personally, the one I like to use is: If you have a column that ALWAYS has data... For instance, Column B is a sequence number which is a REQUIRED field.. Then you can count the non-blank values in that column: RowCnt = Application.worksheetfunction.counta(Range(B:B)) In that case, the Last Row is the number of rows that have a value in column B. - so, you've determined the total number of rows, and set up your loop LastRow = Application.worksheetfunction.counta(Range(B:B)) For RowNum = FirstRow to LastRow
