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
...
Next RowNum
Next, for each of the rows being processed, you need to test the cell with
your criteria
and perform your action based on the cell value:
If (cells(RowNum,"X").value = 5) then
Else
End If
If you record a macro in which you change the range of cells from Y3 to AX3
to "unlocked",
you'll find that is uses:
Range("Y3:AX3").Select
Range("AX3").Activate
Selection.Locked = False
This can be shortened.
You don't need the .activate step, and you don't need to actually "select"
the range of cells:
so it can be shortened to:
Range("Y3:AX3").Locked = False
Then you just have to change the row number (3) to your variable (RowNum)
like:
Range("Y" & RowNum & ":AX" & RowNum).Locked = False
The same thing can be done with:
Range(Cells(RowNum, "Y"), Cells(RowNum, "AX")).Locked = False
If (Cells(RowNum, "X").Value = 5) Then
Range("Y" & RowNum & ":AX" & RowNum).Locked = False
Else
Range(Cells(RowNum, "Y"), Cells(RowNum, "AX")).Locked = True
End If
putting this all together, you end up with:
=================================================================
Option Explicit
Sub Toggle_Locks()
Dim RowNum, FirstRow, LastRow
FirstRow = 1
LastRow = Application.WorksheetFunction.CountA(Range("X:X"))
For RowNum = FirstRow To LastRow
If (Cells(RowNum, "X").Value = 5) Then
Range("Y" & RowNum & ":AX" & RowNum).Locked = False
Else
Range(Cells(RowNum, "Y"), Cells(RowNum, "AX")).Locked = True
End If
Next RowNum
End Sub
=================================================================
(I checked it... in Excel 2010, it processed 100,000 rows in about 15
seconds.)
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
_____
From: Rajan_Verma <rajanverma1...@gmail.com>
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
--
--
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
