Autofill to a variable limit

[LGMan]

Hi, I'm trying to figure out the following. (was orinally posted in a VB forum but was suggested I use this one)

I have a macro within Excel which performs the autofill function such as this:

yearperiod = Application.InputBox _
("Enter the Year Period in the form of Tyyyypp")
Range("B1").Formula = yearperiod

Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B4431")
Range("B1:B4431").Select
Selection.FillDown

I wish to make the lower limit of the autofill a variable that reacts to the last row in column A.

I know that typing =ROW(OFFSET(A1,COUNTA(A:A)-1,0))into any free cell on the worksheet will tell me there are 4431 rows in within column A, but I can't work out how to incorporate this into the macro.

Thanks in advance

 

[PHV]

Have a look in the FAQ area to discover at least 2 ways of getting the last row of a column.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

This will do it:

yearperiod = Application.InputBox _
    ("Enter the Year Period in the form of Tyyyypp")
    
Range([b1], [a65536].End(xlUp).Offset(0, 1)) = yearperiod

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Actually, you could shorten the code even further like this

[Range([b1], [a65536].End(xlUp).Offset(, 1)) = _
    InputBox("Enter the Year Period in the form of Tyyyypp")

But if you use a variable you can check that the users are at least inputting the correct format.

YearPrompt:
yearperiod = InputBox("Enter the Year Period in the form of Tyyyypp")
If Not yearperiod Like "?####??" Then
    MsgBox "You have entered an incorrect format. Please try again"
    GoTo YearPrompt
End If
Range([b1], [a65536].End(xlUp).Offset(, 1)) = yearperiod

? represents any single character, # represents any single numerical character


You can expand on the verification to make it as precise as you want.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[LGMan]

Thanks PH and John both for the speed of your replies.