Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Syntax | Excel Discussion (Misc queries) | |||
Syntax HELP | Excel Discussion (Misc queries) | |||
Help with syntax | Excel Programming | |||
Syntax help for if | Excel Programming | |||
VBA syntax | Excel Programming |