下面的For Next内容为什么会提示错误

nanfanglang

Sub main()
Application.ScreenUpdating = False
Sheets("sheet1").Select
t = Timer
Dim re() As Single, arr
xrow = Range("e:e").End(xlDown).Row
arr = Range("e1:e" & xrow)
ReDim re(1 To xrow)

For i = 1 To xrow
re(i) = arr(i, 1)
Next i

Range("m3") = Timer - t
MsgBox "程序运行时间：" & Range("m3")
End Sub

把For Next加上去之后会提示下面错误：
运行时错误'13':
类型不匹配

-------------------------------------------------------
目标是想把E列的数值赋给re（i）数组，工作表在附件里。
请高人给回答一下，错误在什么地方呢？
另外问下，其它函数和过程能调用re（i）吗？
谢谢啦！

win2009

ReDim re(1 To xrow,1 to 1)

mineshine

Dim re() As Single 不自行定义
改
Dim re() 由系统自行定义

nanfanglang

还是有问题

mjzxlmg

nanfanglang 发表于 2012-1-17 10:55
还是有问题

dim re()


For i = 1 To xrow
ReDim Preserve re(1 To xrow)
re(i) = arr(i, 1)
Next i
  这样得出结果是横向的。

zhaogang1960

nanfanglang 发表于 2012-1-17 10:55
还是有问题

如果想把E列的所有数值(含标题)，2楼、3楼方法都可以
如果确实想定义re() As Single ，arr不能包含标题：

1. Sub main()
   
2.     Application.ScreenUpdating = False
   
3.     Sheets("sheet1").Select
   
4.     t = Timer
   
5.     Dim re() As Single, arr
   
6.     xrow = Range("e:e").End(xlDown).Row
   
7. '    arr = Range("e1:e" & xrow)
   
8.     arr = Range("e2:e" & xrow)
   
9.     ReDim re(1 To xrow - 1)
   
10.     For i = 1 To xrow - 1
    
11.         re(i) = arr(i, 1)
    
12.     Next i
    
13.     Range("m3") = Timer - t
    
14.     MsgBox "程序运行时间：" & Range("m3")
    
15. End Sub
    

复制代码

nanfanglang

您好  帮忙看看下面  如何在别的模块引用re（i）数组呢？运行下面的代码到了re（i）说没定义（子过程或函数没定义）

Sub main()
    Application.ScreenUpdating = False
    Sheets("sheet1").Select
    t = Timer
    Dim re() As Single, arr
    xrow = Range("e:e").End(xlDown).Row
'    arr = Range("e1:e" & xrow)
    arr = Range("e2:e" & xrow)
    ReDim re(1 To xrow - 1)
    For i = 1 To xrow - 1
        re(i) = arr(i, 1)
    Next i
    For i = 500 To xrow - 1
    youhua (i)
'调用youhua函数
Next i

    Range("m3") = Timer - t
    MsgBox "程序运行时间：" & Range("m3")
End Sub



Public Function youhua(ByVal kd As Integer) As Integer
Dim i, k, l, EMAA, EMAB As Integer
Dim sy, sumb, sums As Single
Dim rm(), rn(), ro(), rp(), rq(), rr(), rs(), rt(), ru(), arr(12 To 100, 20 To 200) As Single
xrow = Range("e:e").End(xlDown).Row
ReDim rm(1 To xrow - 1), rn(1 To xrow - 1), ro(1 To xrow - 1), rp(1 To xrow - 1), rq(1 To xrow - 1), rr(1 To xrow - 1), rs(1 To xrow - 1)
ReDim rt(1 To xrow - 1), ru(1 To xrow - 1)

         For i = 11 To kd
             If i = 11 Then
               rm(i) = re(i)
             rn(i) = re(i)            
             Else
              rm(i) = rm(i - 1) * (12 - 1) / (12 + 1) + re(i) * 2 / (12 + 1)
              rn(i) = rn(i - 1) * (26 - 1) / (26 + 1) + re(i) * 2 / (26 + 1)
             End If
        
         If i > 25 Then ro(i) = rm(i) - rn(i)
                 If ro(i) > 0 Then
                 rq(i) = 1
                 Else: If ro(i) < 0 Then rq(i) = -1
                 End If
      
          Next i
         
End Function

zhaogang1960

nanfanglang 发表于 2012-1-17 15:03
您好  帮忙看看下面  如何在别的模块引用re（i）数组呢？运行下面的代码到了re（i）说没定义（子过程或函数 ...

在模块的最顶部声明数组:

1. Dim re() As Single
   
2. 
   
3. Sub main()
   
4.      Application.ScreenUpdating = False
   
5.      Sheets("sheet1").Select
   
6.      t = Timer
   
7.      Dim arr
   
8.      xrow = Range("e:e").End(xlDown).Row
   
9.      arr = Range("e2:e" & xrow)
   
10.      ReDim re(1 To xrow - 1)
    
11.      For i = 1 To xrow - 1
    
12.          re(i) = arr(i, 1)
    
13.      Next i
    
14.      For i = 500 To xrow - 1
    
15.      youhua (i)
    
16. '调用youhua函数
    
17. Next i
    
18. 
    
19.     Range("m3") = Timer - t
    
20.      MsgBox "程序运行时间：" & Range("m3")
    
21. End Sub
    
22. 
    
23. 
    
24. 
    
25. Public Function youhua(ByVal kd As Integer) As Integer
    
26. Dim i, k, l, EMAA, EMAB As Integer
    
27. Dim sy, sumb, sums As Single
    
28. Dim rm(), rn(), ro(), rp(), rq(), rr(), rs(), rt(), ru(), arr(12 To 100, 20 To 200) As Single
    
29. xrow = Range("e:e").End(xlDown).Row
    
30. ReDim rm(1 To xrow - 1), rn(1 To xrow - 1), ro(1 To xrow - 1), rp(1 To xrow - 1), rq(1 To xrow - 1), rr(1 To xrow - 1), rs(1 To xrow - 1)
    
31. ReDim rt(1 To xrow - 1), ru(1 To xrow - 1)
    
32. 
    
33.          For i = 11 To kd
    
34.               If i = 11 Then
    
35.                 rm(i) = re(i)
    
36.               rn(i) = re(i)
    
37.               Else
    
38.                rm(i) = rm(i - 1) * (12 - 1) / (12 + 1) + re(i) * 2 / (12 + 1)
    
39.                rn(i) = rn(i - 1) * (26 - 1) / (26 + 1) + re(i) * 2 / (26 + 1)
    
40.               End If
    
41.          
    
42.           If i > 25 Then ro(i) = rm(i) - rn(i)
    
43.                   If ro(i) > 0 Then
    
44.                   rq(i) = 1
    
45.                   Else: If ro(i) < 0 Then rq(i) = -1
    
46.                   End If
    
47.         
    
48.           Next i
    
49.          
    
50. End Function
    
51. 
    

复制代码