Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
Please try this..
Sub Test() For Each Row In Selection ActiveCell.Offset(1, 0).Activate Next End Sub If this post helps click Yes --------------- Jacob Skaria "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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 "Jacob Skaria" wrote in message ... Please try this.. Sub Test() For Each Row In Selection ActiveCell.Offset(1, 0).Activate Next End Sub If this post helps click Yes --------------- Jacob Skaria "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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
"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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner problem trying to iterate through a selection
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterate over irregular shaped range problem | Excel Programming | |||
Right click and iterate through selection | Excel Programming | |||
Beginner programmer problem | Excel Programming | |||
Beginner Variable Problem | Excel Programming |