范围乘法VB.NET(此代码有什么问题?) [英] Range Multiplication VB.NET (What is wrong with this code?)

问题描述

(VB Express级别:初学者)

(VB Express Level: Beginner)

我想执行以下操作

工作簿1中的一列
一个

A column from Workbook 1
a

b

c

d

Workbook2中的一列

A column from Workbook2
e

f

g

h

输出到单个单元格

ae + bf + cg + dh

ae+bf+cg+dh

(输出是Sumproduct.)

(The output is a Sumproduct.)

工作簿1中有44行，工作簿2中有44行.但是工作簿1中有3列，工作簿2中有104列.工作簿3中的每一列必须与工作簿2中的104列相乘.

There are 44 rows in workbook 1 and 44 rows in workbook 2. But there are 3 columns in workbook 1 and 104 columns in workbook 2. Each column in workbook 3 must be multiplied with 104 columns from workbook 2.

以下是我的工作，它在列的所有单元格中写入相同值.我的理解是我的for循环在某处是错误的.但我无法弄清楚出了什么问题.

Following is my effort, which writes sames values in all the cells of a column. My understanding is my for loop is wrong somewhere. But I am not able to figure out what is wrong.

'link to workbooks
oWB5 = oXL5.Workbooks.Open("D:\1.xlsx")
oWB6 = oXL6.Workbooks.Open("D:\2.xlsx")
oWB7 = oXL7.Workbooks.Open("D:\outputs.xlsx")

'link to worksheets
oSheet5 = oWB5.Worksheets("Inputs")
oSheet6 = oWB6.Worksheets("Coef")
oSheet7 = oWB7.Worksheets("Sheet1")

' ranges to be considerd,
' oWB5 range C22 to C66
' oWB6 range C3 to C47
' oWB7 range C2 to C104 (because there are 104 columns in oWB6) 

'multiplication of ranges

For j = 3 To 47
For i = 2 to 104
For k = 2 to 4 
For l = 22 to 66
oSheet7.Cells(i, k).Value = (oSheet5.Cells(l, k).value * oSheet6.Cells(j, i+1).value) + (oSheet5.Cells(l+1, k).value * oSheet6.Cells(j + 1, i+1).value)
Next
Next
Next
Next 

我们将非常感谢您的帮助.我现在正为此苦苦挣扎两天.

Help will be really appreciated. I am struggling with this for two days now.

推荐答案

这里是使用Excel的SUMPRODUCT公式的简单方法.这样，您就可以让Excel为您完成肮脏的工作.这样做的好处是可以为您节省很多循环:)

Here is a simple method using the Excel's SUMPRODUCT formula. This way you let Excel do the dirty work for you. The advantage of this is that it saves you a lot of looping :)

尝试并测试

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application    
        Dim oWB5 As Excel.Workbook = xlApp.Workbooks.Open("C:\1.xlsx")
        Dim oWB6 As Excel.Workbook = xlApp.Workbooks.Open("C:\2.xlsx")
        Dim oWB7 As Excel.Workbook = xlApp.Workbooks.Open("C:\outputs.xlsx")    
        Dim oSheet7 As Excel.Worksheet = oWB7.Worksheets("Sheet1")

        With oSheet7
            For i = 1 To 8
                For j = 1 To 104
                    Dim Col1Nm As String = Split(.Cells(, j).Address, "$")(1)
                    Dim Col2NM As String = Split(.Cells(, i).Address, "$")(1)
                    .Cells(i, j).Value = xlApp.Evaluate("=SUMPRODUCT(([1]Inputs!" & Col1Nm & "1:" & _
                    Col1Nm & "44)*([2]Coef!" & Col2NM & "1:" & Col2NM & "44))")
                Next
            Next    
        End With

        '~~> Close workbook and quit Excel
        oWB5.Close (False)
        oWB6.Close (False)
        oWB7.Close (True)

        xlApp.Quit()

        '~~> Clean Up
        releaseObject (oWB5)
        releaseObject (oWB6)
        releaseObject (oWB7)
        releaseObject (xlApp)

        MessageBox.Show("done")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

IMP注意:如果您的实际文件名不是1.xlsx和2.xlsx，请更改此部分代码

IMP NOTE: If your actual file names are not 1.xlsx and 2.xlsx then change this part of the code

xlApp.Evaluate("=SUMPRODUCT(([1]Inputs!" & Col1Nm & "1:" & _
Col1Nm & "44)*([2]Coef!" & Col2NM & "1:" & Col2NM & "44))")

替换

1. [1]通过[实际文件名]
2. [2]通过[实际文件名]
3. 通过[实际工作表名称]和
输入
4. 按[实际工作表名称]的坐标

这篇关于范围乘法VB.NET(此代码有什么问题?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！