IJR:
I reproduce your code :
sub get_cell_values()
dim n,k as range
set k=columns("D
"
'
for each n in k
if n.value=1 then n.font.bold = true
next k
1. Next n IS incorrect - k is the counter variable for the loop
2. You dont even have to put 'Next k' just 'Next' will do -Excel remembers which level of loop the Next statement refers to
Now for the interesting part:
When we've defined k as a range, EACH n in k can only mean cell - a collection can contain only things of the same type.
for example we can say "For each ch in Activesheet.ChartObjects" to cycle thru each embedded chart in a worksheet - ch in nothing defined in xl but in the context of the collection ChartObjects it can be nothing but a single chartobject. This is a good thing the designers of Excel have done. So your code simply would be:
sub get_cell_values()
dim n,k as range
set k=columns("D
"
'
for each n in k
if n.value=1 then n.font.bold = true
next
Now consider the statement:
dim n,k as range
by not putting 'n as range' u declared n as a variant - now this type of variable is a chameleon - a variant can literally hold (signify) anything - from a single char, a number, an object, a range, a worksheet, or even an entire a workbook - it can hold anything that can be declared with a Dim statement.
So, contrary to Yakpol's view - its not NOT declaring n as a range that's causing the problem 'coz as I said, a variant can also stand for a range.
Now consider the statement:
set k=columns("D
"
You see, a columns collection can only contain columns even if k is dimensioned as a range (after all, a column is also a range, as is a row)
So the variant n can only mean a full column at a time.
To clarify if we said:
set k=columns("D:F"
In this case the variant 'n' will loop THRICE, for D, E and F. Now since we are checking in the code to make bold if the value is 1 - a cell can have a value but not an ENTIRE column - hence the error msg.
What u should do is use thd following:
Sub get_cell_values1()
Dim n, k As Range
Set k = Range("D
"
.SpecialCells(xlCellTypeConstants, xlNumbers) 'IN KEEPING WITH ANOTHER THEME IN THIS THREAD
For Each n In k
If n.Value = 1 Then n.Font.Bold = True
Next n
End Sub
or
Sub get_cell_values2()
Dim k As Range REM 'n' REMOVED
Set k = Range("D
"
.SpecialCells(xlCellTypeConstants, xlNumbers)
For Each n In k
If n.Value = 1 Then n.Font.Bold = True
Next 'REM 'n' REMOVED
End Sub
Note that I've changed 'Set k = Range("D
"
' to 'Set k = Range("D
"
'. Mull over this in light of the foregoing discussion - u'll get it.
I hope I'm clear - by this time MY head is throbbing.
so 'For each n in
