求助自动查找对应sheet进行数据筛选

switchfoot

现状：
我有两个表格，分别为：表一、表二。
表一是我要处理的表格，表二是数据源。

表一中有多个SHEET，要在表一的每个SHHET中的D列左侧插入一列。

表二中也有很多SHEET，但是与表一的SHEET名称顺序不一致，但名称一致。

目前我的做法是用IF+VLOOKUP实现数据筛选，即：
=IF(VLOOKUP(B:B,'[表二.xls]1'!$B:$C,2,0)<C3,VLOOKUP(B:B,'表二.xls]1'!$B:$C,2,0),"")
这是在SHEET名称1中的公式，公式输入在新插入的一列中，即：D2中输入。

现在的问题是，我用同样的工时复制到SHEET2中，公式中的1，我需要手动改成2。如果要是100个sheet的话，我就要改100次。

寻求帮助，能否将这个变为一键执行。即：自动在D列左侧插入一列，之后根据SHEET名称，自动在数据源表（表二）中查找对应的SHEET 名称，在对应的SHEET里面去筛选与维修内容对应数据的工时费。

我上面的公式意思（上面公式意思是，在数据表中（表二）的sheet名为1的表格中，根据表一B列的关键字，查找小于表一工时费。即：$B:$C,2,0。最后在插入列中只显示小于表一的工时费即可。）

hfwufanhf2006

1、新增列是不必要的，放在最后空白列就好了；
2、每个表只需要改一次公式，其实也只要改下表名和引用范围的下标，其他都复制，我觉得还好吧；
     写代码说不好，涉及文件操作的都没那么简单，检索、打开、关闭对新手都比较麻烦；
     你这类数据结构，看起来都是随性所为，并没有经过认真考虑，所以我感觉只是个临时过渡，专门写代码不值得；

switchfoot

hfwufanhf2006 发表于 2020-6-3 12:23
1、新增列是不必要的，放在最后空白列就好了；
2、每个表只需要改一次公式，其实也只要改下表名和引用范围 ...

大神，这是常态化的工作，不是随性的。

hfwufanhf2006

switchfoot 发表于 2020-6-3 12:29
大神，这是常态化的工作，不是随性的。

我也不是很懂你的状况，如果是常态，改一次公式就可以了，后续无非是把扩大的使用范围的公式再补充复制，可以一次性把公式复制到足够多的行；
有可能你两个表每次都是全新的，那么这样的数据结构在我看来就有随性的味道了。常态化的工作起码要保证一个表的数据结构是固化的，不然你如何写代码？

switchfoot

大神，我这个代码返回的数据不是执行的插一列，而是往后移动了一个位置，我不会改了。您能帮我看看嘛

switchfoot

hfwufanhf2006 发表于 2020-6-3 12:46
我也不是很懂你的状况，如果是常态，改一次公式就可以了，后续无非是把扩大的使用范围的公式再补充复制， ...

麻烦您帮我看一下

hfwufanhf2006

switchfoot 发表于 2020-6-3 13:04
麻烦您帮我看一下

插入一列和把数据往后移动一列并没有实质区别，是一个意思；
插入列需要有插入的实际动作，也会影响后续数据的读取。往后移动一列，则不需要插入的动作，但需要改变参数的位置；
两者半径八两，就看你对哪个更熟了；

从截图看，
brr(i,1)=arr(i,1)
brr(i,2)=arr(i,2)
brr(i,3)=arr(i,3)   '前面 3 列 都是一一对应的
然后，


if d(ssht.name & brr(i,2))<brr(i,3) then   '判断数据的大小
   brr(i,4)=...   '把第4列数据存在数组的第 4 列
endif
再后面是：
brr(i,5)=arr(i,4)
brr(i,6)=arr(i,5)
brr(i,7)=arr(i,6)   '这 3 列 都错开了一位，说明在数组中特意把第 4 列留给了新数据


最后，
ssht.[a1].resize(......)=brr   '就是把生成的新数据再写回表格中，新数据包含了新增的第4列，相当于数据插入了一列，也可以看成是往后移动了一列


这个代码没什么好改的，只要能达到效果就行。作者明显是习惯使用数组的，改成插入动作的方式就不符合这段代码的风格了；

switchfoot

hfwufanhf2006 发表于 2020-6-3 13:39
插入一列和把数据往后移动一列并没有实质区别，是一个意思；
插入列需要有插入的实际动作，也会影响后续 ...

但是我需要插入一列，因为这一页的数据是分上下两部分的，这样做完之后，会导致上部门的数据与下部分的数据列位不一样了。

switchfoot

hfwufanhf2006 发表于 2020-6-3 13:39
插入一列和把数据往后移动一列并没有实质区别，是一个意思；
插入列需要有插入的实际动作，也会影响后续 ...

Sub TEST()
Set WB = Workbooks("表二.xls")
For I = 1 To Sheets.Count
    With Sheets(I)
        .Select
        .Columns("D:D").Insert
        ARR = WB.Sheets(CStr(.Name)).UsedRange
        For K = 2 To .UsedRange.Rows.Count
            For J = 2 To UBound(ARR)
                If ARR(J, 2) = .Cells(K, 2) And ARR(J, 3) < .Cells(K, 3) Then .Cells(K, 4) = ARR(J, 3)
            Next
        Next
    End With
Next
End Sub

大神，我改了代码，但是为什么运行到第二句就提示报错了呢？
提示下标越界
提示语句是“Set WB = Workbooks("表二.xls")”

hfwufanhf2006

switchfoot 发表于 2020-6-3 13:59
但是我需要插入一列，因为这一页的数据是分上下两部分的，这样做完之后，会导致上部门的数据与下部分的数 ...

我看不懂你的意思，插入一列和把数据往后移动一列就是等效的，不存在列无法对齐的问题；
我估计你问题的重点不是如何移动数据，或许是与标题或者别的什么东西有关。写这个代码的人估计也没搞懂；