Calculate columns automatically based on the first column

[richardwm]

Hi Dear All,

I have an excel sheet that is feeded by an Access table, so the number of the rows is variable.

I am looking for a code that calculate IDs based on the first ID (123 in this example).

So, the idea is to replace 1000 with (123 + 1) and 2000 with (123 + 1 +1) .. to the last column that is not empty.

The first ID is always in A2 Range.


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]TEST1[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]TEST2[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]TEST3[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]TEST4[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]TEST5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance

 

[Fishboy]

Hi Dear All,

I have an excel sheet that is feeded by an Access table, so the number of the rows is variable.

I am looking for a code that calculate IDs based on the first ID (123 in this example).

So, the idea is to replace 1000 with (123 + 1) and 2000 with (123 + 1 +1) .. to the last column that is not empty.

The first ID is always in A2 Range.


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]TEST1[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]TEST2[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]TEST3[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]TEST4[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]TEST5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance

Hi Richard, welcome to the boards.

How about something like this?

Sub UpdateColumn()
' Defines variables
Dim LastRow As Long, Rng As Range


' Defines LastRow as the last row of data based on column A
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


' Sets Rng As A3 to the last row of A
Set Rng = Range("A3:A" & LastRow)
' Updates cell A3 with a formula A2+1
Range("A3").Formula = "=A2+1"
' Copy A3
Range("A3").Copy
' Paste from A3 to the last row of A
Rng.PasteSpecial xlPasteAll
' Copy A3 to the last row of A
Rng.Copy
' Paste as values
Rng.PasteSpecial xlPasteValues


End Sub

 

[richardwm]

Hi Fishboy, thank you eternaly. It's exactly what I wanted !!

 

[Fishboy]

Hi Fishboy, thank you eternaly. It's exactly what I wanted !!

Happy to help

 

[Peter_SSs]

Here's simpler, more direct, way for you to consider too.

Sub IncrementIDs()
  Range("A2", Range("A" & Rows.Count).End(xlUp)).DataSeries Type:=xlLinear, Step:=1
End Sub

 

[Fishboy]

Here's simpler, more direct, way for you to consider too.

Sub IncrementIDs()
  Range("A2", Range("A" & Rows.Count).End(xlUp)).DataSeries Type:=xlLinear, Step:=1
End Sub

Genius! I didn't even realise that was a thing!

 

[Peter_SSs]

Genius! I didn't even realise that was a thing!

On the Home ribbon tab, Editing group, look at Fill -> Series...