Increment Row Numbers

[Craigr]

Hi,

I'm a noob with VBA so i could do with some help. I have created a survey and once the user clicks submit the values will be copied across to a table. All i have so far is the table headers. I need a macro that once clicked, it will look at the current row number in the A column, if the row number has a value, it will take that value and +1 in the next row. If there aren't any rows yet, it will need to start the first row as 1.

Could anyone help please?

[PCI]

Can you prepare a short sample of you file to see how to manage the data.

[Craigr]

Can you prepare a short sample of you file to see how to manage the data.

It's just a small survey that uses combo boxes/option buttons. Once the survey has been completed, it gets transferred to a results page which keeps a tally of the results in the survey using cell link.

I just need to know to create an incrementing row number everytime the submit button is clicked.

[JOHN H. DAVIS]

This will increment, however it starts at row 2.

Please Login or Register  to view this content.

Hope that helps.

[PCI]

See how to have the next availble row

Please Login or Register  to view this content.

[Craigr]

Can you prepare a short sample of you file to see how to manage the data.

It's just a small survey that uses combo boxes/option buttons. Once the survey has been completed, it gets transferred to a results page which keeps a tally of the results in the survey using cell link.

I just need to know to create an incrementing row number everytime the submit button is clicked.

[Craigr]

This is the type of thing i want it to do in pseudo-ish terms lol.

If A2 <> 1
Make A2 = 1
Else
Get next empty cell
Empty cell = previous cell + 1

[PCI]

OK try

Please Login or Register  to view this content.

if you want to start at row1 with value 1

Please Login or Register  to view this content.

[Craigr]

OK try

Please Login or Register  to view this content.

if you want to start at row1 with value 1

Please Login or Register  to view this content.

Edit - Thanks, that works!

[siby.cb]

I have created a survey and once the user clicks submit the values will be copied across to a table. I have a macro that once clicked, it will look at the current row number in the C column, if the row number has a value, it will take that value and +1 in the next row. If there aren't any rows yet, it will need to start the first row as 1.
But I wanted to have another code, which will create a row number along with a specific format. For example, if some one submit the value, it should create an ID some thing like ABC001 or ABC002...etc. How should I edit my code. I am attaching my code below.

Option Explicit

Sub StartNewRecord()
Dim inputWks As Worksheet
Dim listWks As Worksheet
Dim rngClear As Range
Dim rngNext As Range
Dim rngID As Range

Set inputWks = Worksheets("Input")
Set listWks = Worksheets("LookupLists")

Set rngClear = inputWks.Range("DataEntryClear")
Set rngID = inputWks.Range("IDNum")
Set rngNext = listWks.Range("NextID")

rngClear.ClearContents
rngID.Value = rngNext.Value

inputWks.Activate
rngID.Offset(1, 0).Activate

End Sub