Need a macro to add 1 to active cell based on previous cell contents

[wtom]

Hi All,

I am VERY inexperienced with VBA, but I do like to dabble.

I would like to have a macro (called "Add New Quote Number"), and have it do the following...

I press the macro button

It looks for the end of the column (B) - I know this code [Range("b65536").End(xlUp).Offset(1).Select]

This may end up at B91 whose content might be "Quote Number 091" for example.

I need B92 to equal "Quote Number 092" (based on B91 + 1)

I hope this makes sense, and I thank you all in advance for your assistance.

Cheers

WTOM

[humdingaling]

what your asking sounds very familiar to this question posed
http://www.excelforum.com/excel-prog...invoicing.html

if that doesn't answer your question please provide sample file for inputs and outputs look like

[alansidman]

try this:

Please Login or Register  to view this content.

[humdingaling]

slightly different because of quote number

Please Login or Register  to view this content.

[wtom]

Hi humdingaling, that works well, except for the text "Quote Number". I need the macro to copy the above cells text as it appears then +1 to it.

The text part will differ yearly.

For example, this year, the quote number format is "RCH-15-xxxx", (where "RCH" relates to the business unit, "15" relates to the current financial year, and "xxxx" relates to the incrementing four digit quote number. Next year it will be "RCH-16-xxxx" with "16" denoting the next fin year.

So, if the text in cell B23 is currently "RCH-15-0145" and it is at the end if the column, after the macro runs, B24 would become "RCH-15-0146", and so each time the macro is invoked.

I hope that makes sense?


Cheers

WT

[Vikas_Gautam]

Ok then Try this..

Please Login or Register  to view this content.

[wtom]

Perfect Vikas, thank you so much!!

[HaHoBe]

Hi,

I still wonder why people indicating to use a version of 2007 or greater use the limitation of pre 2007 files.

Please Login or Register  to view this content.

Ciao,
Holger

[Vikas_Gautam]

Thanks for the response WTOM
However, Holgers approach is more good too..