Adding a button to find string in column and fill out the first empty cell

[vaznlyfe]

Hey there,
I'm trying to create a button that would do a string search through Column A (task serial numbers) to determine the next task serial number and insert it into the first empty cell in the column. An example of the column is shown below:

Column A
1 Task#
2 12-1
3 12-2
4 12-3
5 12-4
6 10-1
7 10-2
8 10-3
9 11-1

The number to the left of the hyphen stands for the year and the number to the right of the hyphen stands for the task number for that year. So for any additional tasks assigned this year, the serial number has to start with 12 and the number after the hyphen has to be 1 higher than the previous task. In case of the example above, clicking on the button should insert "12-5" into cell A10. If there hasn't been any tasks assigned for this year (no 12 in front of hyphen), then clicking the button should add the first task serial number (12-1) for 2012 in A10. I'm sure it's a simple solution but I just cannot figure out as I'm still a newbie to VBA. I'd appreciate any help on this. The codes I have been playing with:

Please Login or Register  to view this content.

[tigeravatar]

vaznlyfe,

Give this a try:

Please Login or Register  to view this content.

[vaznlyfe]

Wow tigeravatar, you are awesome. That's exactly what I need. Thanks alot!

[vaznlyfe]

After filling out the task serial number, is it possible to get excel to automatically fill in the person's name (displayed in Sheet tab) the next cell over, and then scroll to and select the description cell (3rd cell over) so the person can fill in his/her task description? Thanks.

Task #	Person Assigned	Task Description
12-5	John	Create task list
12-6	John	Create VBA
12-7	John	|

[tigeravatar]

vaznlyfe,

Yes, that is possible. How does Excel know which name to choose though?

[vaznlyfe]

Can it pull the name from the name of the active sheet?

[tigeravatar]

Ooh, ok. So the worksheet is named "John". Yeah, give this code a try. Note that it has an input box prompting for the description.

Please Login or Register  to view this content.

[vaznlyfe]

Everything is good except I dont need the inputbox. Regardless of where I'm at in the excel sheet, when I click that button I just need for the screen to scroll to that row (maybe have the row in the center of the screen?) and double clicked on the task description cell so that it is ready for user input.

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[vaznlyfe]

Once again, you came to the rescue. Thank you so much.

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save