Excel VBA阵列循环故障排除:使用Redim和UBound一维和二维阵列 [英] Excel VBA Array Loop Troubleshooting: Using Redim and UBound, 1- and 2-dimensional arrays

问题描述

我来自 Stackover主题执行更快的宏.我得到了答案，但是此代码无法正常工作，我正在问你，(我已尝试修复)

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

错误为: 运行时错误'9'下标超出范围
调试为: objIE.navigate"https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item="&搜索字词(a)

已修复

   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

推荐答案

有一些问题.

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

上面的代码创建Search_Terms(1到121，1到1)的二维数组.可以用

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)

这会将2-D数组转换为CopiedData(1至122)的新空白1-D数组.

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

现在进入循环.

For a = 1 To UBound(Search_Terms) + 1

此处的ubound排名第一(例如1到121).您不能转到Search_Terms(122，1)，因为它不存在.因此，当 a 变为122时，以下崩溃会导致运行时错误'9'下标超出范围.

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)

可能的解决方案

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

创建Search_Terms(1到121)的一维数组.

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)

您已对A1:A121进行了转置，将其放入一维数组中.有意义的是，您需要转置相同的一维数组以将其放回到B1:B121中.

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.

这篇关于Excel VBA阵列循环故障排除:使用Redim和UBound一维和二维阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！