Excel VBA:读取10行数据，运行条件循环，打印输出值
Excel VBA:读取10行数据，运行条件循环，打印输出值
提问于 2013-02-15 06:40:39
我正在尝试将值读入子函数，运行条件循环，然后在工作表中打印输出值。然而，我遇到了一些问题。在子函数infiltPrint中，我似乎无法获得要在单元格中打印的值。
Option Explicit
Dim dz() As Double
Dim WC() As Double
Dim fc() As Double
Dim NL, i As Integer
Dim sumdrain As Double
Dim infl As Double
Sub main()
Call InfiltRead
Call infilt
Call Infiltprint
End Sub
Sub InfiltRead()
Dim dz() As Double
Dim WC() As Double
Dim fc() As Double
Dim NL, i As Integer
Dim sumdrain As Double
sumdrain = 0
Dim infl As Double
'read inputs
NL = 10
infl = Cells(2, 1)
Application.ScreenUpdating = False
Worksheets("Sheet1").Activate
ReDim dz(NL)
ReDim WC(NL)
ReDim fc(NL)
For i = 1 To NL
dz(i) = Cells(1 + i, 3)
WC(i) = Cells(1 + i, 7)
fc(i) = Cells(1 + i, 5)
Next i
End Sub
Sub infilt()
Dim j As Integer
j = 1
While (infl > 0) And (j <= NL)
If infl > (fc(i) - WC(j)) * dz(j) Then
infl = infl - (fc(i) - WC(j)) * dz(j)
WC(j) = fc(i)
Else
WC(j) = WC(j) + infl / dz(j): infl = 0
End If
j = j + 1
Wend
If infl > 0 Then
sumdrain = sumdrain + infl
infl = 0
End If
End Sub
Sub Infiltprint()
Dim col As Double
Dim rw As Double
col = 7
For rw = 2 To 11
Cells(rw + 1, col).Value = WC()
Next rw
End Sub我一直在“Cells(Row + 1，col) = WC()”这一行中遇到类型不匹配的问题。
我知道这可能不是我代码中唯一的错误。坦率地说，我不知道如何从infilt sub打印我的值。我试着用一条副干道。
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-02-15 12:31:50
将打印以下版本的代码。它改变了下面提到的几件事。
您遇到的主要问题是将WC数组复制到工作表。您试图将整个数组分配给单个单元格。
修复方法是将整个WC数组分配给您想要放入的完整单元格集合。为了实现这一点，必须对WC进行转置。通常需要将WC作为变量("Dim WC as Variant")来将数组分配给一个范围，新代码反映了这一点。
我还清除了一些变量的重新声明，并更改了三个数组的索引方式，以使数组的上下限与您的计数器对齐。
在intfilt()子函数中，您使用i作为循环中的索引，但是没有初始化或迭代它，这一点也发生了变化。
cpearson.com是一个组织良好和深入的资源，用于VBA编码-包括处理数组。你可能会想去看看。
Option Explicit
Dim dz() As Double
Dim WC As Variant
Dim fc() As Double
Dim NL As Long, i As Long '<- Integer gets converted to Long internally, so do it directly
'<- Way you did it results in Variant type for i
Dim sumdrain As Double
Dim infl As Double
Sub main()
InfiltRead <- "Call" not needed
infilt
Infiltprint
End Sub
Sub InfiltRead()
sumdrain = 0 '<- variables in this sub are already declared, don't redeclare
'read inputs
NL = 10
infl = Cells(2, 1).Value
Application.ScreenUpdating = False ' <- if processing lots of data, would want to turn off auto sheet recalculation as well
Worksheets("Sheet1").Activate
ReDim dz(1 To NL) '<- set lower bound to avoid default value of 0
ReDim WC(1 To NL) '
ReDim fc(1 To NL)
For i = 1 To NL
dz(i) = Cells(1 + i, 3).Value '<- could assign each range directy to an array, but this ok
WC(i) = Cells(1 + i, 7).Value
fc(i) = Cells(1 + i, 5).Value
Next i
Application.ScreenUpdating = True '<- cleanup code
End Sub
Sub infilt()
Dim j As Long
i = 1 '<- DEBUG placeholder - you used i in this sub, but did not initialize or iterate it
j = 1
While (infl > 0) And (j <= NL)
If infl > (fc(i) - WC(j)) * dz(j) Then
infl = infl - (fc(i) - WC(j)) * dz(j)
WC(j) = fc(i)
Else
WC(j) = WC(j) + infl / dz(j)
infl = 0
End If
j = j + 1
i = i + 1 '<- DEBUG placeholder iteration
Wend
If infl > 0 Then
sumdrain = sumdrain + infl
infl = 0
End If
End Sub
Sub Infiltprint()
Dim col As Long
Dim rw As Long
rw = 2
col = 9 '<- Changed from 7 to 9 for debugging (so original values would not be overwritten)
Range(Cells(rw, col), Cells(rw + NL - 1, col)).Value = _
Application.Transpose(WC) '<- needed to get proper references for assignment
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14885421
复制相关文章
相似问题
腾讯云开发者
