AutoFill a Column

[pdieppa]

Hi there

This is my first post; hopefully it will not be my last as I see there is very good information here!

I have a simple request from one our remote offices.

C2 Contains either a single or three character letter
D2 contains a number
E2 contains a number

I need to combine the letter(s) with the number (C2 & D2) to become one and autofill A column with sequential numbers up to the value of E2.

Example:

If I type E in C2 and 1000 in D2 and 200 in E2, I need 200 numbers like this:

E1000
E1001
E1002
...
E1200

I'll be attaching this code to a button.

Thanks for your help in advance.

Percy

 

[Oorang]

=C2&D2&ROW()

 

[Von Pookie]

For a macro, try:

Sub Combine()
Dim Ltr As String, Num As Long, lRow As Long

Ltr = Range("C2").Value
Num = Range("D2").Value
lRow = 1
For Num = Num To Range("E2").Value
    Cells(lRow, "A").Value = Ltr & Num
    lRow = lRow + 1
Next Num

End Sub

 

[Barrie Davidson]

You can also try

Dim FillRange As Range

Set FillRange = Range("G1")
Set FillRange = FillRange.Resize(Range("E2"))
FillRange.Item(1, 1).Select
FillRange.Formula = "=$C$2&($D$2+ROW()-ROW(" & _
    FillRange.Item(1, 1).Address & "))"
FillRange.Copy
FillRange.PasteSpecial xlPasteValues
Application.CutCopyMode = False

Just change Range("G1") to the proper range to copy the data.