Results 1 to 3 of 3

Thread: Ubound Defined by Variable in Array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Ubound Defined by Variable in Array

    I am trying to build this array that can be different for each set of data that is consantly growing. This means that I need to have a Ubound that is also growing. Unfortunately when trying to use the counter method to determine how big the Ubound should be everytime I kepp getting an error "Constant expression required". any thoughts?
    Do While InStr(ActiveCell.Value, "Column") = 0
    a = a + 1
    ActivCell.Offset(0, 1).Select
    Loop


    Dim FC(0 To a) As Double

    For i = LBound(FC) To UBound(FC)
    If InStr(ActiveCell.Value, "Column") = 0 Then
    FC(i) = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    Else
    Exit For
    End If
    i = i + 1
    Next i

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,225
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Ubound Defined by Variable in Array

    I help you with your code, but better explain why you need to fill out the arrangement.

    Note: You must select a cell where there is a row with the word "Column", if it does not enter a loop.

    Code:
      cell = ActiveCell.Address
      Do While InStr(ActiveCell.Value, "Column") = 0
        a = a + 1
        ActiveCell.Offset(0, 1).Select
      Loop
      a = a - 1
      ReDim FC(0 To a) As Double
      
      Range(cell).Select
      For i = LBound(FC) To UBound(FC)
        FC(i) = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
      Next i
    Regards Dante Amor

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,225
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Ubound Defined by Variable in Array

    With the following you can put the data in an array a :

    Code:
    Sub test3()
      a = Range(ActiveCell, Cells(ActiveCell.Row, Application.Match("*Column*", Rows(ActiveCell.Row), 0) - 1))
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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