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
- Use of Excel Analysis Tool Pack components in VBA
- Help needed for a VBA novice with excel charting
- ODBC excel VBA
- Convert Excel VBA procedure to a VB Script procedure that runs
- Using Excel VBA to chart a moving annual total
- excel-vba examples
- VBA Excel
- hyperlink to a VBA macro, on an Excel sheet
- Excel VBA performance issue
- Looking for a spell checker for an excel/vba userform textboxes
- VBA Excel Project to Users
- Excel VBA books pdf format
- UDFs in Excel VBA
- Access 7 to Access 2003 using Excel VBA
- Controlling Outlook Express with VBA/Excel
- Closing Excel Window using VBA
- Problem using VB6 (not VBA) to put picture file into Excel
- how do you secure excel vba code...totally?
- VBA and Excel Solver