Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoFill based on Cell Value (Excel 2000)

    Hi All ...

    In cell B2 I have a number that can change.

    I need to autofill a given selection by that cell's value
    I recorded the macro autofilling from row 7 to row 15.


    Range("A7:H7").Select
    Selection.AutoFill Destination:=Range("A7:H15"), Type:=xlFillDefault

    I will always start at row 7, but depending on the value in cell B2, I will need to vary the ending row.

    Can someone help me, please.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: AutoFill based on Cell Value (Excel 2000)

    Cat

    How about using a Select case to determine the number of rows needed:

    Example:

    Sub ExampleOfSelectCase()
    Select Case ThisWorkbook.Worksheets("Sheet1").Range("B2").Valu e
    Case Is = 1 '/If B2 has 1
    lHowManyRows = 15
    Case Is = 2, 3, 4 '/If B2 is 2 or 3 or 4
    lHowManyRows = 25
    Case 5 To 10 '/If B2 is betwen 5 and 10
    lHowManyRows = 100
    Case Else '/If Be is outside the range.
    lHowManyRows = 1
    End Select
    End Sub

    Now surely you will need to change the ThisWorkbook.Worksheets("Sheet1") to match with your sheet name and also the line(s) lHowManyRows = 15 will need the right amount of rows.

    Then the line you wrote:
    Selection.AutoFill Destination:=Range("A7:H15"), Type:=xlFillDefault
    becomes:
    Selection.AutoFill Destination:=Range("A7:H" <font color=red> & lHowManyRows </font color=red> ), Type:=xlFillDefault

    Would you like to see a picture of my Sebastian?

    HTH...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: AutoFill based on Cell Value (Excel 2000)

    Cat
    Sorry I forgot you may need this [/b] line
    Dim lHowManyRows As Long

    Under the
    Sub ExampleOfSelectCase() line

    like in
    Sub ExampleOfSelectCase()
    Dim lHowManyRows As Long


    Sorry about that...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFill based on Cell Value (Excel 2000)

    What exactly is in cell B2, and how does it tell you where to fill to?

    If you always fill to column H, and B2 contains the row number, then you could use something like this:

    <pre> Range("A7:H" & Range("B2").Value).AutoFill Destination := Range("A7:H" & Range("B2").Value), Type := xlFillDefault
    </pre>

    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFill based on Cell Value (Excel 2000)

    Thanks Wassim, ... again, you have been very helpful!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •