Excel VBA Array Problem

  • Hi All,
    I have used arrays before and can usually get looping them working without issue.
    I am currently trying to read a range of cells into an array, this part seems to be working as it is generating the correct number of entries. However, when I try to msgBox myself any entries I get an error (Run Time Error 9 - SubScript out of range), not sure what is going wrong with this... Any help appreciated.
    Thanks in advance!

    Sub createArray()
    Dim allSites
    Dim siteLoop
    allSites = Range("A2", Range("A2").End(xlDown))
    For siteLoop = LBound(allSites) To UBound(allSites)
    MsgBox (allSites(2))
    Next siteLoop
    End Sub
  • Re: Excel VBA Array Problem

    Just as a bit of background - I am manipulating some data through this macro and one of the fields is the name of the different sites. This is currently held in an array with the values declared in VBA. However the names can change in the database and if they do the script messes up as it no longer matches the array. This bit of script is supposed to read the list of sites exported from the database into the array so this is no longer an issue.

  • Re: Excel VBA Array Problem

    As StephenR said you create a 2-D array - to make it easier you can transpose it into a 1-D array

    allSites = Application.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))
    MsgBox allSites(siteLoop)

    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

Participate now!

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