Need help with syntax

[James]

Hi everyone. Ive seen stuff like this but im not sure how to implement it
correctly. I need to look in row 7 & cycle through each column (with text).
Something like:

'VS previously set
'RowNum previously set

For each ColumnNum in row 7
if cell(7, ColumnNum).Value = VS then
Cells(RowNum, ColumnNum).Locked = True
End if
Next

Ive seen something like this before and i understand what is goin on but
usually the "ColumnNum" part in the for statement is never declared or set to
anything...why is this? Thanks for the help!

 

[Don Guillett]

try this
Sub valuesinrow()
mr = 7
For i = 1 To Cells(mr, Columns.Count).End(xlToLeft).Column
If Cells(mr, i)) = VS Then Cells(mr, i).Locked = True
'If ucase(Cells(mr, i)) = "VS" Then Cells(mr, i).Locked = True
Next i
End Sub

 

[Sam Wilson]

If you have Option Explicit turned on then it does need to be declared,
otherwise you'd get a compile error.

But generally, in the case of "For.. Next..." loops you don't need to assign
a value to the variable. eg

dim wb as workbook
set wb = activeworkbook

dim ws as worksheet
for each ws in wb.worksheets
msgbox ws.name
next ws


Here, you don't assign a value to ws at any point, but when the code runs it
will take be each worksheet in turn. Note that you also don't need to know
how many worksheets are in the workbook wb.

Sam

 

[Roger Govier]

Hi James

What you have posted will not work. Column number does need to be set and
incremented.
You could use something like

Sub lockcells()
Dim i As Long, j As Long
j = 7 ' row number
For i = 1 To 26 ' number of columns
If Cells(j, i) = VS Then
Cells(j, i).Locked = False
End If
Next

End Sub

 

[Rick Rothstein \(MVP - VB\)]

You can iterate the Columns collection and not specifically number the
columns. I would think this would work...

Dim ColumnNum As Range
For Each ColumnNum In Rows(7).Columns
If Cells(7, ColumnNum.Column).Value = VS Then
Cells(RowNum, ColumnNum).Locked = True
End If
Next

Rick

 

[Rick Rothstein \(MVP - VB\)]

I think you are looking for this construction...

' VS previously set
' RowNum previously set
Dim ColumnNum As Range
For Each ColumnNum In Rows(7).Columns
If Cells(7, ColumnNum.Column).Value = VS Then
Cells(RowNum, ColumnNum).Locked = True
End If
Next

Rick

 

[Roger Govier]

Hi Rick

Thanks.
I have never used that construct, and it was the missing .Columns and
..Column that threw me.
Wouldn't this cycle through all 256 columns of the row (or 16,000+ for
XL2007) though?

I liked Don's solution of carrying out the Columns count to determine the
end of the loop.

 

[Rick Rothstein \(MVP - VB\)]

Yes, it would iterate across every cell in the row, that is true. I'm not
entirely sure that is as bad as it sounds though. VB seems to be able to
iterate collections quite efficiently (it seems to be some kind of built-in
optimization)... I'm not sure how that compares to direct reads of (what may
be) a large number of individual cells, one at a time, via the Cells
property.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Of course, the code I posted could actually take advantage of any collection
efficiency IF I HAD USED IT! This is the code I should have posted...

Dim C As Range
For Each C In Rows(7).Columns
If C.Value <> "" Then
Cells(RowNum, C.Column).Locked = True
End If
Next

Now the member of the collection is actually being used for something. Note
I changed the iteration variable name from ColumnNum to C because it is a
cell.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, this is the code construction you should use, not the one I posted
earlier...

Dim C As Range
For Each C In Rows(7).Columns
If C.Value <> "" Then
Cells(RowNum, C.Column).Locked = True
End If
Next

This version actually makes use of the Rows(7).Columns collection member as
the collection is being iterated (my previously posted code failed to do
that).

Rick