Copy/Paste in Row Before and After Cell

[nicotob]

The image below will serve as the basis for what I am trying to achieve. Using a macro, within a row (selected by the user) I would like to locate a cell with a value and paste that value in the cell before and the cell after it. For example, I would like to have "1" copied into A1 and C1, "2" copied into D1 and F1, etc. I have used the following code to place the value in the preceding cell, but it breaks down when I try to apply it to the subsequent cell.

A	B	C	D	E	F	G	H	I
	1			2			3

[6StringJazzer]

Your code doesn't have to literally copy & paste, you can just assign the value.

Please Login or Register  to view this content.

[nicotob]

The code that you provided will result in 1's being assigned to every cell in the range due to the fact that we have assigned a value to a cell in front of the "for loop." It is this precise problem that I am trying to overcome.

[6StringJazzer]

Good point. I focused on the fact that you did not have code to assign the cell to the right. I hadn't tested it (always helps if you include your workbook for testing purposes).

How about this:

Please Login or Register  to view this content.

[nicotob]

Thank you very much. That does it. I was hoping you might be able to help me understand ho the Selection.Special Cells(xlCellTypeConstants) achieved the desired result. Why does this additional code cause the for loop to miss the cells with values placed in cells ahead of the loop?

Thanks again for your help.

[6StringJazzer]

The attribute SpecialCells(xlCellTypeConstants) returns a range of cells within Selection containing only constants (i.e., not empty and not formulas). This attribute is evaluated one time at the beginning of the For loop, so will be not be reevaluated as you fill in additional cells in the Selection range--it's locked in when you start.