MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

AutoFill in Excel VBA

  Asked By: Erica    Date: Sep 05    Category: MS Office    Views: 3446

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?
i have an idea, but i don't think i can add + 1 to a alphanumeric data



6 Answers Found

Answer #1    Answered By: Baden Smith     Answered On: Sep 05

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.


Answer #2    Answered By: Zeke Thompson     Answered On: Sep 05

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..

Answer #3    Answered By: Amir Hashmi     Answered On: Sep 05

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

Post your code so we can look at it.

Answer #4    Answered By: Jarryd Williams     Answered On: Sep 05

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.


Answer #5    Answered By: Kate Johnston     Answered On: Sep 05

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()
Selection.AutoFill Destination:=Range("A1:A9"), Type:=xlFillDefault
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.

Answer #6    Answered By: Gavril Bonkob     Answered On: Sep 05

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.