Auto fill Variable Cell in Excell

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Hutch
    New Member
    • Mar 2007
    • 74

    Auto fill Variable Cell in Excell

    So using VBA i have the following code.

    Sub F1102C()
    ActiveCell.Form ulaR1C1 = "F1102C-5-1"
    Range("A2").Sel ect
    ActiveCell.Form ulaR1C1 = "A1-2NH-7"
    Range("B2").Sel ect
    ActiveCell.Form ulaR1C1 = "A1-C2"
    Range("C2").Sel ect
    ActiveCell.Form ulaR1C1 = "5-1"
    Range("D2").Sel ect
    ActiveCell.Form ulaR1C1 = "HS-1"
    Range("E2").Sel ect
    End Sub

    This works by running the Macro on a selected box placing the main body portion of the number in that cell and then placing the following items in order below that cell. The problem is no matter what cell I have selected when i run this macro the "fill" always happens from A2 and so on and so forth.
    I want it to happen below the selected box each time. Thanks in Advance.
  • kadghar
    Recognized Expert Top Contributor
    • Apr 2007
    • 1302

    #2
    Sure, there you go

    [CODE=vb]Sub F1102C()
    Dim i As Long
    Dim j As Long
    i = ActiveCell.Row
    j = ActiveCell.Colu mn
    ActiveCell.Form ulaR1C1 = "F1102C-5-1"
    Cells(i, j).Select
    ActiveCell.Form ulaR1C1 = "A1-2NH-7"
    Cells(i + 1, j).Select
    ActiveCell.Form ulaR1C1 = "A1-C2"
    Cells(i + 2, j).Select
    ActiveCell.Form ulaR1C1 = "5-1"
    Cells(i + 3, j).Select
    ActiveCell.Form ulaR1C1 = "HS-1"
    Cells(i + 4, j).Select
    End Sub[/CODE]

    Good Luck
    Kd

    Comment

    Working...