VBA code to generat transaction number

[nbeharry]

I need help to write a code in excel VBA to have my spreadsheet generate a Transaction number down column A.
The transaction number should start with 1 and each row below should be 1+ the previous row’s number. Thanks.

 

[shrivallabha]

You can easily do this without VBA.

Suppose your transaction row starts in A2 then manually enter 1 in that cell and then in cell A3 write below formula and copy down as much you want.
=IF(B3<>"",A2+1,"")

As soon as you enter something in column B, transaction number will appear.

 

[chirayu]

why don't you just write 1 in cell A1 & write the formula =A1+1 in cell A2 & drag down

 

[nbeharry]

why don't you just write 1 in cell A1 & write the formula =A1+1 in cell A2 & drag down

Thanks. I already have a vba code that is using my source worksheet to populate my destination worksheet but since the source worksheet doesn't have that "Transaction Number" field, I would much rather incorporate a code in my existing vba to do it for me.
Is there a way or do I have to manually go to my destination worksheet and write that formula? Thanks.

 

[Chihiro]

There is a way. But without knowing your code or data structure. We will have to guess at what's best suited for you.

Upload sample file with enough data and code, you will get answer better suited to your needs that way.

 

[nbeharry]

Here is a sample of my data. Of course, I have about 1000 lines. I need to populate the destination worksheet using the source worksheet. Thanks for your help.

 

[Chihiro]

With your set up, I'd go with Shrivallabha's suggestion and use the formula, just have it filled up to 2000 line or so, his formula will show blank unless you've got information in Col B. No need to manually update each time.

If you need VBA method, paste your code using CODE tag. Code will depend on how you are transferring data from Source to Destination.

 

[nbeharry]

Hi Chihiro, thanks for you help but I cannot view the code. The code tag button doesn't seem to take me to the code. Please let me know how to view it. Thanks.

 

[Chihiro]

Oh, I thought you had code to transfer the information without the Transaction Number.

Ok, here's formula based approach. Which I believe would be much easier for you to maintain.

As more data is added to source, you just have to drag and extend table range on Destination sheet to populate data. When you see blank row appear, it means you've reached end of data in Source sheet.

 

[nbeharry]

Thank you for your help.