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")