Beginner problem trying to iterate through a selection

S

Steve

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?
 
J

Jacob Skaria

Please try this..

Sub Test()
For Each Row In Selection
ActiveCell.Offset(1, 0).Activate
Next
End Sub

If this post helps click Yes
 
D

Dave Peterson

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
 
S

Stephen

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
 
S

Stephen

Thank you, Jacob, but the goal wasn't to get to the final cell, but in
actually getting both steps to work seperately. See my reply to Dave in
order to understand why.


Steve
 
D

Dave Peterson

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.
 
S

Steve

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
 
D

Dave Peterson

"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.
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
Click to expand...
 
S

Steve

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
 
Top