CODEWITHSUNDEEP
vbaexcel
Ömer Faruk iskender
Ömer Faruk iskender

Reputation: 61

Excel VBA Array Loop Troubleshooting: Using Redim and UBound, 1- and 2-dimensional arrays

I came here from Stackover Topic to doing faster macro. I got answer but this code not working and i am asking to you, (i tried to fix)

   Sub Faster_Method()

Dim objIE As InternetExplorer
Dim Prc1 As String
Set objIE = New InternetExplorer
Dim Search_Terms() As Variant
Dim CopiedData() As Variant

objIE.Visible = True


Search_Terms() = ActiveSheet.Range("A1:A121").Value
ReDim CopiedData(1 To UBound(Search_Terms) + 1)

For a = 1 To UBound(Search_Terms) + 1

objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)
Do: DoEvents: Loop Until objIE.readyState = 4
Prc1 = objIE.document.getElementsByClassName("item-amount")(0).innerText
CopiedData(a) = Prc1

Next

ActiveSheet.Range(Cells(1, 2), Cells(UBound(CopiedData), 2)).Value = CopiedData

objIE.Quit

End Sub

And the error is : run time error '9' subscript out of range
Debug is : objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)

Fixed

   Sub Faster_Method()

Dim objIE As InternetExplorer
Dim Prc1 As String
Set objIE = New InternetExplorer
Dim Search_Terms() As Variant
Dim CopiedData() As Variant


Dim y As Integer
objIE.Visible = True


Search_Terms = Application.Transpose(ActiveSheet.Range("A1:A121").Value)

ReDim CopiedData(LBound(Search_Terms) To UBound(Search_Terms))


y = 1


For a = LBound(Search_Terms) To UBound(Search_Terms)
objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)
Do: DoEvents: Loop Until objIE.readyState = 4
Prc1 = objIE.document.getElementsByClassName("item-amount")(0).innerText
Sheets("Sheet1").Range("B" & y).Value = Prc1

y = y + 1

Next

ActiveSheet.Range(Cells(1, 2), Cells(UBound(CopiedData), 2)) = Application.Transpose(CopiedData)

objIE.Quit

End Sub

Upvotes: 1

Views: 582

Answers (2)

elliot svensson
elliot svensson

Reputation: 603

There may be an issue with the result of getElementsByClassName not being compatible with CopiedData(). I suggest eliminating CopiedData() and saving prc1 directly to Excel cells to see if that eliminates the wrong data type error.

Upvotes: 0

user4039065
user4039065

Reputation:

There are a few problems.

Search_Terms() = ActiveSheet.Range("A1:A121").Value

The above code creates a 2-D array of Search_Terms(1 to 121, 1 to 1). This can be verified with,

debug.print lbound(Search_Terms, 1) & " to " & ubound(Search_Terms, 1)
debug.print lbound(Search_Terms, 2) & " to " & ubound(Search_Terms, 2)

Next you attempt to reshape a new array with,

ReDim CopiedData(1 To UBound(Search_Terms) + 1)

This converts the 2-D array to a new blank 1-D array of CopiedData(1 to 122).

Now you go into a loop.

For a = 1 To UBound(Search_Terms) + 1

The ubound here is the first rank (e.g. 1 to 121). You cannot go to Search_Terms(122, 1) because it doesn't exist. So when a becomes 122 the following crashes with run time error '9' subscript out of range.

objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)

Possible solution(s)

Search_Terms = application.transpose(ActiveSheet.Range("A1:A121").Value)

Creates a 1-D array of Search_Terms(1 to 121).

ReDim CopiedData(LBound(Search_Terms) To UBound(Search_Terms))

Reshape the target array to the same dimensions.

For a = LBound(Search_Terms) To UBound(Search_Terms)

Don't go outside of the Lower Boundary or the Upper Boundary.

ActiveSheet.Range(Cells(1, 2), Cells(UBound(CopiedData), 2)) = application.transpose(CopiedData)

You transposed the A1:A121 to get it into a 1-D array. It makes sense you need to transpose an identical 1-D array to put it back into B1:B121.

Upvotes: 3

Related Questions