细胞增殖打印细胞输出- VBA Excel

Question

Sub MVCalc()

On Error Resume Next

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rowcount As Integer
Dim result As Long

Verification1:
Set rng1 = Application.Selection
Set rng1 = Application.InputBox("Select Market Prices", "MV Calculator", Type:=8)
If rng1.Columns.Count > 1 Then
MsgBox ("Please select only one column")
Set rng1 = Nothing
GoTo Verification1
End If

Verification2:
Set rng2 = Application.Selection
Set rng2 = Application.InputBox("Select Position/Shares", "MV Calculator", Type:=8)
If rng1.Columns.Count > 1 Then
MsgBox ("Please select only one column")
Set rng2 = Nothing
GoTo Verification2
End If

Verification3:
Set rng3 = Application.Selection
Set rng3 = Application.InputBox("Select output area", "Mv Calculator", Type:=8)
If rng3.Columns.Count > 1 Then
MsgBox ("Please select only one column")
Set rng3 = Nothing
GoTo Verification3
End If


If rng1.Rows.Count < rng2.Rows.Count Then
MsgBox ("Too many Position/Shares entries, please check again")
Set rng1 = Nothing
Set rng2 = Nothing
Set rng3 = Nothing
GoTo Verification1

ElseIf rng1.Rows.Count > rng2.Rows.Count Then
MsgBox ("Not enough Position/Shares entries, please check again")
Set rng1 = Nothing
Set rng2 = Nothing
Set rng3 = Nothing
GoTo Verification1

ElseIf rng3.Rows.Count <> rng1.Rows.Count Then
MsgBox ("Output range doesn't match the number of Market Value or Price entries, please redo")
Set rng1 = Nothing
Set rng2 = Nothing
Set rng3 = Nothing
GoTo Verification1

End If

rowcount = rng1.Rows.Count
Dim table()
ReDim table(1 To rowcount, 0)

For i = 1 To rowcount
table(i, 0) = Val(rng1.Cells(i, 1)) * Val(rng2.Cells(i, 1))
Next i

Dim rng4 As Range
Set rng4 = rng3(1).Resize(1, rowcount)
rng4 = table


End Sub

这就是我现在想要的，有什么建议吗？我已经更改了您建议的内容，并且仍然得到了水平行中的第一个条目，而不是各种值相乘的列。我不知道什么是问题，不足以补充任何建议。

Answer 1

结果是一个数字变量。通过循环重置相同变量的值，直到完成为止，然后将此单个值分配给rng3，该值将进入所选范围的第一个单元格。

在循环之前，设置一个数组并在循环中填充它：

Dim Array()
Redim Array(1 to rowcount,0) ' make it 2-dimentional so you do not need to rotate it later
For i = 1 To rowcount
    Array(i, 0) = Val(rng1.Cells(i, 1)) * Val(rng2.Cells(i, 1))
Next i
' I do not know what rng3 refers to or it's size. We know where it starts.
Dim rngOut as Range
Set rngOut = rng3(1).Resize(1, rowcount) ' a vertical column one-cell wide
rngOut = Array