|
楼主 |
发表于 2011-4-3 22:59
|
显示全部楼层
本帖最后由 lhj323323 于 2011-4-3 23:01 编辑
回复 lhj323323 的帖子
老师:我写的下面这一段虽能导出结果,请问
下面这段程序还需要怎么优化和提速,同时如何在在[操作面]的J列显示出我想要的数据来
我是逐一对两个数据源进行历遍,分别导入到数组Arr1中,这个办法是否有点笨?
Sub yy()
Dim aa$, arr, Arr1(), Myr As Long, k%, j% 'Myr&
Dim Myr2&, Arr2, x%
Application.ScreenUpdating = False
On Error Resume Next
Set Sht1 = Sheets("数据源A")
Set Sht2 = Sheets("操作面")
Set Sht3 = Sheets("结果表")
'情况一:用户指定一支股票代码,测试成功
'aa = Sht2.[i8].Value
'情况二:用户指定多支股票代码,测试成功
Myr2 = Sht2.[i65536].End(xlUp).Row
Arr2 = Sht2.Range("i6:i" & Myr2)
'========主程序A===========================
With Sht1
Myr = .Range("a65536").End(xlUp).Row
arr = .Range("a2:g" & Myr)
For i = 1 To UBound(arr) '先历遍数据源
For x = 1 To UBound(Arr2)
'情况一:用户指定一支股票代码,测试成功
'If CStr(arr(i, 4)) = CStr(aa) Then '针对用户仅指定一支股票的代码
'情况二:用户指定多支股票代码,测试成功
If InStr(arr(i, 4), Arr2(x, 1)) > 0 Then '针对用户指定多支股票的代码
k = k + 1
ReDim Preserve Arr1(1 To 7, 1 To k)
For j = 1 To 7
Arr1(j, k) = arr(i, j)
Next
GoTo 100 '我加的<<<<<<<如是情况一,就不用此句
End If
Next x '我加的<<<<<<<如是情况一,就不用此句
100: '我加的<<<<<<<如是情况一,就不用此句
Next i
End With
'========主程序B===========================
With Sheet4
Myr = .Range("a65536").End(xlUp).Row
arr = .Range("a2:g" & Myr)
For i = 1 To UBound(arr) '先历遍数据源
For x = 1 To UBound(Arr2) '再历遍[网络首页]的K列指定的股票代码,<<<<<<<如是情况一,就不用此句
'情况二:用户指定多支股票代码,测试成功
If InStr(arr(i, 4), Arr2(x, 1)) > 0 Then '针对用户指定多支股票的代码
k = k + 1
ReDim Preserve Arr1(1 To 7, 1 To k)
For j = 1 To 7
Arr1(j, k) = arr(i, j)
Next
GoTo 200 '我加的<<<<<<<如是情况一,就不用此句
End If
Next x '我加的<<<<<<<如是情况一,就不用此句
200: '我加的<<<<<<<如是情况一,就不用此句
Next i
End With
With Sht3
.Cells.Clear
.[a1:g1].Value = Sht1.[a1:g1].Value
.[a2].Resize(UBound(Arr1, 2), UBound(Arr1, 1)) = Application.Transpose(Arr1)
.Columns("d:d").NumberFormatLocal = "000000"
.Rows.Font.Name = "宋体"
.Rows.Font.Size = 10
End With
Application.ScreenUpdating = True
Sht3.Activate
End Sub
|
|