Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill Down (2002/SP3)

    I have a spreadsheet that contains information on several teams and associated info. I need to split this into teams and then pull back a random sample of each teams data.

    The macro to split the date into separate team sheets works fine
    The VB code to generate the random sample is tried and tested:
    It pulls a random sample from a population and each sample is pulled only once
    It transfers the sample to a separate worksheet based on unique identifier in (this case) column A
    My problem is in creating the unique identifier in column A
    I need to fill down (1, 2, 3) in column A to match the length of the data on the population sheet, e.g. if the split out Team 1 data fills B2:H300 I need to create a fill down on range A2:A300.
    The row length for each team will be different
    This information is required each quarter and again the data length will be different each time.

    All help gratefully received.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill Down (2002/SP3)

    Here is a macro to do this:

    Sub FillColA()
    Dim wsh As Worksheet
    Dim lngMaxRow As Long
    Dim i As Long
    Set wsh = ActiveSheet
    lngMaxRow = wsh.Range("B65536").End(xlUp).Row
    For i = 2 To lngMaxRow
    wsh.Cells(i, 1).Value = i - 1
    Next i
    End Sub

    If you want to apply it to another worksheet than the active one, replace ActiveSheet with Worksheets("SheetName").

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fill Down (2002/SP3)

    An alternative is to add code like this

    <pre>Range("a2") = 1
    Range("a3") = 2
    Range("A2:A3").AutoFill _
    Destination:=Range(Range("A2"), _
    Range("B65536").End(xlUp).Offset(0, -1))</pre>



    Steve

  4. #4
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Down (2002/SP3)

    Hi

    Many thanks both solutions work fine.

Posting Permissions

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