Need help with syntax
 

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!

Need help with syntax
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
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!

Need help with syntax
 

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

"James" wrote:

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!

Need help with syntax
 

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

--
Regards
Roger Govier

"James" wrote in message
...
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!

Need help with syntax
 

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


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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

--
Regards
Roger Govier

"James" wrote in message
...
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!

Need help with syntax
 

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


"James" wrote in message
...
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!

Need help with syntax
 

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.


--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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

--
Regards
Roger Govier

"James" wrote in message
...
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!

Need help with syntax
 

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


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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.


--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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

--
Regards
Roger Govier

"James" wrote in message
...
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!