I need to number a form using autofill, the start cell is static but the end
cell is a variable. I have written the following code but I can't figure out
how to refer to the varable when autofilling. The code selects the entire
range but I can't figure out how to autofill from c15:LastCell. Thank you for
your help.

Sub num2()
'
' num2 Macro
' Macro recorded 10/23/2009 by tpeter
'

Dim LastCell As Range
Set LastCell = Range("C15").End(xlDown)

Range("C15").Select
ActiveCell.FormulaR1C1 = "1"
Range("C16").Select
ActiveCell.FormulaR1C1 = "2"
Range("C15:C16").Select
Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault
Range("C15:C28").Select
End Sub

I usually like to pick a column that has data in it if the row is used. In this
code, I used column A.

Option Explicit
Sub num2()

Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("C15").Value = 1
.Range("C16").Value = 2

.Range("c15:C16").AutoFill _
Destination:=.Range("C15:c" & LastRow), _
Type:=xlFillDefault
End With

End Sub



tpeter wrote:
>
> I need to number a form using autofill, the start cell is static but the end
> cell is a variable. I have written the following code but I can't figure out
> how to refer to the varable when autofilling. The code selects the entire
> range but I can't figure out how to autofill from c15:LastCell. Thank you for
> your help.
>
> Sub num2()
> '
> ' num2 Macro
> ' Macro recorded 10/23/2009 by tpeter
> '
>
> Dim LastCell As Range
> Set LastCell = Range("C15").End(xlDown)
>
> Range("C15").Select
> ActiveCell.FormulaR1C1 = "1"
> Range("C16").Select
> ActiveCell.FormulaR1C1 = "2"
> Range("C15:C16").Select
> Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault
> Range("C15:C28").Select
> End Sub

--

Dave Peterson

Try

Sub Macro1()
Dim lngLastRow As Long
lngLastRow = Range("C15").End(xlDown).Row
Range("C15") = 1
Range("C16") = 2
Range("C15:C16").AutoFill Destination:=Range("C15:C" & _
lngLastRow), Type:=xlFillDefault
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"tpeter" wrote:

> I need to number a form using autofill, the start cell is static but the end
> cell is a variable. I have written the following code but I can't figure out
> how to refer to the varable when autofilling. The code selects the entire
> range but I can't figure out how to autofill from c15:LastCell. Thank you for
> your help.
>
> Sub num2()
> '
> ' num2 Macro
> ' Macro recorded 10/23/2009 by tpeter
> '
>
> Dim LastCell As Range
> Set LastCell = Range("C15").End(xlDown)
>
> Range("C15").Select
> ActiveCell.FormulaR1C1 = "1"
> Range("C16").Select
> ActiveCell.FormulaR1C1 = "2"
> Range("C15:C16").Select
> Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault
> Range("C15:C28").Select
> End Sub

Worked Great Thanks for all of your help. Just a comment to anyone looking at
this,if there is no information in column c this will fill all the way down.

"Jacob Skaria" wrote:

> Try
>
> Sub Macro1()
> Dim lngLastRow As Long
> lngLastRow = Range("C15").End(xlDown).Row
> Range("C15") = 1
> Range("C16") = 2
> Range("C15:C16").AutoFill Destination:=Range("C15:C" & _
> lngLastRow), Type:=xlFillDefault
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "tpeter" wrote:
>
> > I need to number a form using autofill, the start cell is static but the end
> > cell is a variable. I have written the following code but I can't figure out
> > how to refer to the varable when autofilling. The code selects the entire
> > range but I can't figure out how to autofill from c15:LastCell. Thank you for
> > your help.
> >
> > Sub num2()
> > '
> > ' num2 Macro
> > ' Macro recorded 10/23/2009 by tpeter
> > '
> >
> > Dim LastCell As Range
> > Set LastCell = Range("C15").End(xlDown)
> >
> > Range("C15").Select
> > ActiveCell.FormulaR1C1 = "1"
> > Range("C16").Select
> > ActiveCell.FormulaR1C1 = "2"
> > Range("C15:C16").Select
> > Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault
> > Range("C15:C28").Select
> > End Sub