Assigning Values in Array, VBA

  • First time posting so I will apologize up front for not following conventions or doing something incorrectly.

    The below code is to look at a table exported from another program and load the information that it needs into an array for reformatting/use later.

    But when I try to execute I get the subscript out of range error the first time I try to load in a value (ReportInfo(y, 1) = Cells(x, 10) ' Activity). I don't understand why as I've done this in other macros and it has worked. Am I just missing something?

    I'd like to understand why it is happening so I don't need to ask again, as well as help with the code. But please keep something in mind - I'm not a programmer (as I am entirely self taught); I've seen some pretty cool programming that does things more efficiently than I ever could, but I don't have the experience/knowledge base to follow. And I'm getting old. So if you could tell me what is wrong with MY code I would appreciate it.

  • Re: Assigning Values in Array, VBA

    It's much easier to troubleshoot with an actual file that you are having issues with.

    You can enter a small amount of dummy data if the actual data is sensitive.


    Bruce :cool:

  • Re: Assigning Values in Array, VBA

    Isn't the array zero based.

        y = 0
        For x = 6 To TotalRows + 6
            ReportInfo(y, 0) = Cells(x, 10) ' Activity
            ReportInfo(y, 1) = Cells(x, 2) ' Name
            ReportInfo(y, 2) = Cells(x, 6) ' Hours
            y = y + 1
        Next x
  • Re: Assigning Values in Array, VBA

    It fails on the last iteration, not the first, because ReportInfo is zero-based. All you need do is remove the line y=1.

    Edit - NoSparks got there first.

  • Re: Assigning Values in Array, VBA

    It works, and thank you.

    But I hate zero based - it makes things like figuring out row and column numbers difficult. Why can't the y=1 remain and the ReportInfo(0,0) just be empty?

    So, in other words, I redimed the array to TotalRows, but that number includes the 0 row. But I'm not using that, I'm starting at row 1 and going TotalRows more, which is out of it's range, thus the error. So what I really need to do is redim to TotalRows+1 then the y+1 could stay. Correct?

  • Re: Assigning Values in Array, VBA

    Ok, now I know. I will use the Option Base 1. That makes sense to me.

    Thank you everybody for you time, patience, and help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!