从一个excel文件中读取内容并将其粘贴到主excel的一列中 [英] to Read content from one excel file and paste that in master excel in one column

问题描述

我想从excel(当前)中读取一个特定的应用程序，并计算它在excel中出现的次数.
然后在我的主excel中，我已经定义了这些应用程序，我想创建一个名称为"Aug 20"的列，并与excel中的应用程序相对应，应在excel母版中添加计数.
如果当前excel中没有找到应用程序，则在master excel中我想将计数值添加到master excel中的相应应用程序，为0.

请让我知道如何实现.我没有这样做的逻辑.

谢谢！

Hi,

I want to read a particular application from a excel (current) and count it the number of times it has appeared in the excel.
Then in my master excel i have defined these applications, i want to create a column with name as "Aug 20" and corresponding to the application in excel it should add the count in master excel.
If there are not applications found in current excel then in master excel i want to add the count value to corresponding application in master excel as 0.

Please let me know how this can be achieved. I am not getting the logic to do this.

Thanks!

推荐答案

正如我在评论中所写，您仅需要CountIf工作表函数.但是，如果您想使用VBA代码进行操作，那么可以，我将向您展示一个示例.当同时打开两个excel文件(主文件和当前文件)时，将使用以下代码.

复制下面的代码，并将其粘贴到新模块的主文件中:

As i wrote in comment, you need only CountIf worksheet function. But if you would like to do it with VBA code, OK, i''ll show you an example. Below code is to use when both excel files (master and current) are opened.

Copy the code below and paste it into master file in new module :

Dim srcwsh as Worsheet, dstwsh As Worksheet
Dim i as Integer, j As Integer, counter as Integer
Dim sAppName As String

Set dstWsh = ThisWorkbook.Worksheets(1)
Set srcWsh = Application.Workbooks("current.xls").Worksheets(1)
counter = 0
i = 2 'start from row no. 2 in srcwsh
j = 2 'start from row no. 2 in dstwsh
Do While srcwsh.Range("A" & i)<>""
    sAppName = srcwsh.Range("A" & i)
    Do While dstwsh.Range("A" & j)<>""
        If dstwsh.Range("A" & j) = sAppname Then counter = counter + 1
    Loop
    srcwsh.Range("B" & i) = counter
    counter = 0 'new application, so reset counter    
Loop

这不是最佳代码，因为它逐行(逐个单元)比较数据，但显示逻辑".

This is not optimal code, because it compares data row by row (cell by cell), but it shows "logic".

这篇关于从一个excel文件中读取内容并将其粘贴到主excel的一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！