incrementing a column

[geversud]

Is there anyway to increment the value in a range without explicitly looping?

 

[Juan Pablo González]

Well, without knowing what you want, you could use Paste Special, Add or Multiply, depending on what you want/have/need.

 

[Cbrine]

geversud,
You could add the incrementation value to a blank cell. Copy it, then on the values you want to increment just do a paste special, change the operation to add, and press OK. This will increment all values in the paste range by the orginal copied value.

HTH
Cal

 

[geversud]

Basically, I have a worksheet full of parts. In one column is the level of assembly and in the other the partnumber. (level of assembly reads as number 1 for the top most part with all the subcomponents of the assembly as level 2 and and sub components of 2 parts as level 3 and so on) There are many of these worksheets and the column representing the level of assembly is offset by 1 (for example the second part reads as level 1 but should be level 2) this is for all the parts. So in short, what would be the most efficient way to increment this column by 1.

 

[Juan Pablo González]

See Cbrine post.

 

[geversud]

This works well but I would like to avoid having to assign a value to a cell then copy it and paste it. I have to execute this operation hundreds of times and would like not to disrupt the integrity of the sheets beyond incrementing those values in column. Is there another way I can accomplish this strictly in the background through code?

 

[Cbrine]

Sub Increment()
Dim OldValue as String

OldValue = Range("A1").value
Range("A1").value = 1
Range("A1").copy
Range("b1",range("B65535").end(xlup)).pastespecial xlPasteValues,xlPasteSpecialOperationAdd
Range("A1").value =OldValue
End Sub

This will increment all values in column B by 1, each time it is executed.

HTH
Cal

 

[geversud]

Thanks for your help this definitely works out. And I am assuming that there is no other way to do this, less explicitly looping.