Increase Number by one in a form once cleared

**KarenV** · 01-22-2013, 12:18 AM

I have created a form in Excel which once completed they hit a button that has a macro that takes the information from the form and places it at the bottom of the previous data on another 'data' worksheet - that is working well.
What I would like to do in one of the cells in the fill in form is take the last number from column B in the 'data' worksheet and increase it by one and place it in the cell on the fill in form automatically. I have tried a macro and could get it to go to the end of the named ranged and right one cell then copy and paste special (ticking add) back on the form worksheet, but then the macro just remembers the last cell I used and doesn't reset to go back to the bottom of the database range once reset? I hope that makes sense - can anyone help?

thanks
Karen

**:) Sixthsense :)** · 01-22-2013, 12:43 AM

Add this code before end sub of the command button Click Code

Please Login or Register  to view this content.

Change the name of the Textbox1 and sheet and column references to your requirement.

**KarenV** · 01-22-2013, 12:51 AM

Thanks, I have changed the column ref to B but I am unsure what you mean by changing the name of the Textbox1 to? What would that refer to?

**:) Sixthsense :)** · 01-22-2013, 12:55 AM

increase it by one and place it in the cell on the fill in form automatically

Where you would like to show the incremented value? Whether in Form Textbox? Or in the cell of excel sheet?

**KarenV** · 01-22-2013, 01:07 AM

OK, nearly there, it is reading the last date from column A and increasing it by one in column B, but I need it to read the last number in column B, ie it is looking at column A rather than going across to column B? I have changed the letters in the code? I really appreciate your help.

**:) Sixthsense :)** · 01-22-2013, 01:12 AM

Change it in both the places

Please Login or Register  to view this content.

**HaHoBe** · 01-22-2013, 01:16 AM

Hi, Sixthsense,

quite a perfect example of using constants for the column instead of letters in the code:

Please Login or Register  to view this content.

Ciao,
Holger

**:) Sixthsense :)** · 01-22-2013, 01:21 AM

@ HaHoBe,

Hm... Yes Nice Indication of the perfect usage

But I believed OP will aware of the changes since he is already adding data's from userform to sheet

**HaHoBe** · 01-22-2013, 12:29 PM

Hi, Sixthsense,

it must have been too early this morning (6:16 a.m. local time), could you please explain why you use the code like you indicated? Correct me if I´m wrong but by using the end command you will refer to a Range, and the standard of range is Value. So why not simplify to

Please Login or Register  to view this content.

Ciao,
Holger

**:) Sixthsense :)** · 01-22-2013, 11:59 PM

For me your Post #9 Date/Time showing as 01-22-2013 09:59 PM, and I am writing this message to you 01-23-2013 09:29 AM and your's is too early since 12 hours gap.

Hm... another good catch

I don't know why my mind gone for that one where there is no logic when it can be arrived as simple which described in your post #9

Increase Number by one in a form once cleared

LinkBack

Thread Tools

Rate This Thread

Display

Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Re: Increase Number by one in a form once cleared

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions