vba auto increment a number?

Question

i am trying to insert a reference number into a cell in excel using vba. I want a prefix text of 'V0000' followed by an auto incremented number starting from 836.

so for each row that gets inserted I will have V0000836
Then V0000837
etc

A large portion of my code creates a new row and inserts data into it automatically one after the other, but instead of posting my whole code I am just wanting to focus on this one line of code which inserts value into column AA of my spreadsheet. I am using the following but it just gives me V00001 each time. can someone show me how I can get my code to do what I want it to.

ws2.Range("AA" & DestRow).Value = "V0000836" & Value + 1

Answer 1

Using VBA, you can do this by incrementing the number each time the loop goes round and then prefixing the V0000 to the front like so:

Dim i As Integer
Dim cell As Range, rng As Range

Set rng = Range("A1:A10")

i = 836

For Each cell In rng
    cell.Value = "V000" & i
    i = i + 1
Next cell

Answer 2

Consider an alternative that does not remove numerical (mathematical) functionality from the cell's value.

ws2.Range("AA" & DestRow).numberformat = "\V0000000"
ws2.Range("AA" & DestRow).Value = 836 + 1

If you require the formatted (displayed) alphanumeric designation you can retrieve it like this.

Dim str as string, num as long
str = ws2.Range("AA" & DestRow).text    '◄ V0000837
num = ws2.Range("AA" & DestRow).value   '◄ 837