CODEWITHSUNDEEP
excelvba
KDE
KDE

Reputation: 85

How do I redim/preserve my array to prevent run time error 9

I have created an API to grab data from a Wix website (not shown), it then replaces values in the string, before splitting the string into columns via text to column function. It then stores the range in an array, I have added laArray to the watch window and I can see every instance of laArray(1 to 56) but the issue is I have a run time error 9, Subscript out of range when trying to loop through the array in order to have each array value as a vertical list instead of a horizontal one.

I did initially try to use string manipulation instead of text to columns and replace but needed a quick fix so ended up doing it this way.

JSON = Cells(1, 1)
lcReplace = Array("}", "{", "/", "\", """", ":", ";", "items", "[", "]", "_")

For Each A In lcReplace
  JSON = Replace(JSON, A, " ")
Next A

Cells(2, 1) = JSON
Cells(2, 1).Select
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False

g = Cells(4, 1).End(xlToRight).Column

Dim laArray As Variant
laArray = (Range(Cells(4, 1), Cells(4, g)).Value2)
laArray = Application.Transpose(Range(Cells(4, 1), Cells(4, g)))

For f = LBound(laArray) To UBound(laArray)
   Cells(f + 4, 3) = laArray(f)
Next f

Summary:

Run time error 9 when trying to make a cell equal to my array, from what I have read I need to Redim preserve the array, I tried that before and in the f loop but it still returns a run time error, How can I prevent this? Is there an alternative to dimensioning as from what I have read it will make the code rather slow when dimensioning multiple times.

Any help would be appreciated.

Example

Upvotes: 0

Views: 136

Answers (1)

Toddleson
Toddleson

Reputation: 4457

laArray is a 2-dimensional array, as can be confirmed using the Watch window.

For example, if g is 10, then laArray will have a size of 1 to 10, 1 to 1.

You need to specify the second dimension with your current approach.

Cells(f + 4, 3) = laArray(f, 1)

Upvotes: 3

Related Questions