Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I want to create a macro that will automatically add 1 to an

alphanumeric data but will have a limitation for the add..

For Example

on cell a1 = AB0001

on cell a2 = AB0002

on cell a3 = AB0003

and it would have a limit.. because i want to extract all the ranges

from a series... i want to automatically encode this to a column..

for example from AB0001 to AB1000

is this possible?

thanks!

i have an idea, but i don't think i can add + 1 to a alphanumeric data

right?

You arte correct, you can't add 1 to an alphanumeric string.

You need to split it into two strings using Mid, etc. The two alphas and

the numeric part. Then convert the numeric to an integer, add 1 to it, and

convert it back to a zero-fill string using the Format function. Lastly,

put the two bits back together again.

You'll be making this a function, I assume, and therefore putting a call to

it in A2, A3, etc. This means that you probably don't need a separate

limit - you'll limit it by the number of cells you fill with your formulas.

PS, there is an interesting use of Row() to produce the actual result you're

talking about. Put the following formula in A1, then copy/paste it down.

="AB"&TEXT(ROW(),"0000")

I found a way to do that, I used the Mid function to split the

alphanumeric data.. the problem is I used a For Next statement so that I could

copy the rest of the columns.. also, I have to do this every column for every

series.. for example column A series is a0001 - a2000.. therefore it should stop

at a2000 then go to the next column b for the other series x0001-x2000..

Is there a way?

I've been using the If statement Then Else Stop, but the problem with Stop is

that it will stop the whole code.. and not continue to the next column..

Stop will take you into debug mode - you don't want that.

Post your code so we can look at it.

Use the following code: Pl.note, this is logic. If you are not able to make it,

do let me know.I wil lgive you the smae.

Dim Num1=000

Dim Num2=1

Cell a1=AB &Num1 &( Num2+1)

Use this formula to fill the columns you needed.

Regards,

I'm confused by the answer Dave gave ("you can't add 1 to an

alphanumeric string"). This is indeed true (types are mismatched),

but the Subject Line refers to AutoFill. AutoFill can do this..so...

Starting with this:

A1 = AB001

A2 = AB002

This recorded macro:

Sub Auto_Fill()

Range("A1:A2").Select

Selection.AutoFill Destination:=Range("A1:A9"), Type:=xlFillDefault

Range("A1:A9").Select

End Sub

Gives this:

A1 = AB001

A2 = AB002

A3 = AB003

A4 = AB004

A5 = AB005

A6 = AB006

A7 = AB007

A8 = AB008

I do not, however, understand the question about the limit.

I'd gone with the "I want to create a macro that will automatically add 1 to

an alphanumeric data but will have a limitation for the add". With the

assumption that this macro could then be called an "auto fill" (lower case).

Thanks for pointing out that AutoFill itself will do this.

Didn't find what you were looking for?
Find more on AutoFill in Excel VBA
Or get search suggestion and latest updates.

Related Topics:

- Convert code from excel 2000 vba to excel 2003 vba
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Excel VBA Problem in excel 2000
- Add-Ins loaded into Excel
- Autofill Using VB Code Question
- Attaching a File to an Outlook Email using VBA (in Excel)
- Can we make PC to Phone calls using Macro Code in Excel VBA
- writing VBA for excel
- Excel VBA - need to copy paste conditional statement
- Running VBA with an Older Version of Excel
- DDE and VBA in Excel
- Data validations in user forms in Excel VBA
- Good VBA/Excel book(s)
- Obtaining the date modified property of a file from within Excel VBA
- Running Excel VBA macros in OpenOffice Calc
- Excel VBA
- Excel VBA selecting item for web page list box
- VBA Excel Drop down lists
- VBA-Excel 97
- Problem with Excel VBA Creating Subdirectory
- need vba excel code
- error using secondary axis in excel chart (VBA)
- Data validations in user forms in Excel VBA
- Good VBA/Excel book(s)