I understand. Thanks again for your help! With that, I was able to
flesh out the larger script I was working on, and made some improvements
along the way.

Most appreciated!



Steve



"Dave Peterson" wrote in message
...
"Option Explicit" says that I want to be forced to declare all the
variables
that I use. Then I don't have to worry about debugging problems with
(some)
mispelled variables:

myCtr1 = myCtrl + 1
The names are different, but look the same (depending on the font used).
One
ends with the digit one and one ends with a lower case L.

And myCol was left over from the previous suggestion. I didn't notice it
and
didn't delete it.

Steve wrote:

Thank you, Dave! That worked like a charm, and helped me to learn
something!

I have two questions, however:

1. Why is "Option Explicit" there? Does it actually accomplish anything
for this script? It seems to run just fine if I leave it out.

2. Why is "myCol" being declared? It doesn't seem to be used anywhere
in
the script.

Steve

"Dave Peterson" wrote in message
...
First the code I suggested would work on a range with multiple columns.
It
loops through the all the cells in the first column, then it loops
through
all
the cells in the second column, then the third, and so forth.

But I'd still loop through each row once--and one way to to that is to
loop
through each cell in the first column.

And if I were a user, I'd only want to select the single column and as
the
developer, I'd try to make sure that was all that was selected.

So...

Option Explicit
Sub Test()

dim myCell as range
dim myCol as range
Dim myRng as range

set myrng = selection.areas(1).columns(1) 'first column of the first
area

if myrng.column < 1 then
msgbox "not in column A"
exit sub
end if

for each mycell in myrng.cells 'just column A.
'increment the existing value in column B by 1
mycell.offset(0,1).value = mycell.offset(0,1).value + 1

'copy (actually assign) the value in column D to column C
mycell.offset(0,2).value = mycell.offset(0,3).value
next mycell
end sub

You should be able to modify this code to check to make sure that the
selection
is column 3 (same as C) and do the assignment of the values.

Stephen wrote:

Well, I don't have a problem if I'm only working with a single
column
selection. My problems come when I'm working with a multiple column
selection, and I have different things I need to do.

Okay, here's a more detailed setup of the simple script I'm trying
to
accomplish:

The data consists of 4 columns.

Column A: Numeric
Column B: Numeric
Column C: Dates (regular values)
Column D: Dates (formula generated)

What needs to happen is this:

1. Selection will cover all 4 columns.
2. Within the selection, Column B values must be incremented by 1.
3. Within the selection, Column D dates must be copied into Column C
(copy
values only, since Column D is generated by formulas).

Now, I have two child scripts that both work perfectly in limited
scope.

--------------------------
Child Script 1:
Description: With a selection only 1 column wide, increment all
values.

Sub IncrementCells()
'
' Increments cell values within a vertical single column selection
'

For Each cell In Selection

x = ActiveCell.FormulaR1C1
x = x + 1

ActiveCell.FormulaR1C1 = x
ActiveCell.Offset(1, 0).Activate

Next

End Sub

--------------------------
Child Script 2:
Description: With a selection within Column C only 1 column wide,
copy
the values of Column D into Column C.

Sub CopyValuesFromRight()
'
' For each cell in a vertical single column selection, this macro
copies
into it the *value* of the cell to the right of it.
'

For Each cell In Selection

ActiveCell.Offset(0, 1).Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

ActiveCell.Offset(1, 0).Activate

Next

End Sub

--------------------------

So, both of these scripts work just fine, in their limited scope.
My
problem occurred when I tried to combine them, and also assuming that
all
four columns would be selected (even though nothing is happening to
Column
A, it will still be selected).

Here was my attempt to combine them:

-----------------

Sub Test()

' Set the focus to Column B
ActiveCell.Offset(0, 1).Activate

For Each Row In Selection

' Increment Column B
x = ActiveCell.FormulaR1C1
x = x + 1
ActiveCell.FormulaR1C1 = x

' Set the focus to Column C
ActiveCell.Offset(0, 1).Activate

' Set Column C to the date as shown in Column D
ActiveCell.Offset(0, 1).Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Set the focus to the next line, Column B, ready to start
again.
ActiveCell.Offset(1, -1).Activate

Next

End Sub

-----------------

And the problems with this script are what led me to ask my
original
question.

Steve

"Dave Peterson" wrote in message
...
Maybe you don't need to activate the cells.

Option Explicit
Sub Test()

dim myCell as range
dim myCol as range
Dim myRng as range

set myrng = selection

for each mycol in myrng.columns
for each mycell in mycol.cells
mycell.value = "whateveryouwant
next mycell
next mycol
end sub

Steve wrote:

In Excel 2007, in tracing a problem I was having with a script, I
distilled
an issue down to the following:

---------------------------------------------

Sub Test()

For Each Row In Selection

ActiveCell.Offset(0, 1).Activate
ActiveCell.Offset(1, -1).Activate

Next

End Sub

---------------------------------------------

If I select a 2x2 range of cells, what I expect is that when
stepping
through the above code, it changes the focus of the selection from
(using
these as relative references to the selected range) A1 to A2 to B1
to
B2
and
then exiting the script.

However, it does not update the row it thinks it's working on until
it
goes
through it twice. Which means that for a selection that's 2 rows
high,
it
loops 4 times, instead of 2.

What am I doing wrong?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson