• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to generat transaction number

nbeharry

New Member
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.
 
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.
 
why don't you just write 1 in cell A1 & write the formula =A1+1 in cell A2 & drag down
 
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.
 
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.
 
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.
 

Attachments

  • Test_Upload.xls
    29 KB · Views: 11
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 tagupload_2016-3-7_14-44-11.png. Code will depend on how you are transferring data from Source to Destination.
 
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.
 
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.
 

Attachments

  • Test_Upload (1).xls
    32.5 KB · Views: 10
Back
Top