Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Code to store new text (Excel xp)

    I need to search through Column I, cells 2 to 20. range("I2:I20")

    col i and j below
    i j
    1 hi
    1 bye
    1 me
    1 you
    1 body
    2 me
    2 you
    2 free
    2 be
    3 a
    3 b
    4
    4 fry
    5 me
    5 you
    1 bye
    1 cry
    6 me
    6
    6 you
    6 more

    and then fill an array with the contents of 1 cell to the right of the number, but only the last instance of the number that occurs in COL i.

    So in the case above, the array would contain

    cry, be, b, fry, you, more

    I do not know how to write the code. Can someone please help me with it. Thank you.

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

    Re: Code to store new text (Excel xp)

    Try this:

    Sub FillArray()
    Dim arr() As String
    Dim lngMinRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long
    Dim lngMinIndex As Long
    Dim lngMaxIndex As Long
    Dim i As Long

    ' column I
    lngCol = 9
    ' start in row 2
    lngMinRow = 2
    ' end in row 20
    lngMaxRow = 20
    ' optional: determine last filled row automatically
    ' lngMaxRow = Cells(65536, lngCol).End(xlUp).Row
    ' determine min and max index for array
    lngMinIndex = Application.WorksheetFunction.Min _
    (Range(Cells(lngMinRow, lngCol), Cells(lngMaxRow, lngCol)))
    lngMaxIndex = Application.WorksheetFunction.Max _
    (Range(Cells(lngMinRow, lngCol), Cells(lngMaxRow, lngCol)))
    ' redimension array
    ReDim arr(lngMinIndex To lngMaxIndex)
    ' loop through cells in column I to fill array
    For i = lngMinRow To lngMaxRow
    arr(Cells(i, lngCol)) = Cells(i, lngCol + 1)
    Next i

    ' for testing purposes
    For i = lngMinIndex To lngMaxIndex
    Debug.Print i, arr(i)
    Next i
    End Sub

Posting Permissions

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