VBA找不到工作 [英] VBA Find not working

问题描述

我有一本包含所有分支机构的所有发票清单的工作簿，我们称之为一切，基本上我需要搜索包含每个分行发票的另一个文件中的发票。它实际上是每个分支的文件，每个文件按月份分割，我需要检查每个工作表，然后在一个单元格中插入一个值。让我们为每个分支打电话给这个0001等等。

一切文件基本上包含一个分支号码列，一个带发票号码，一个有发票号码与发行人代码和一个说，如果它在分支机构文件中找到。分支文件包含与分支号相同的最后一列，如果发票是否在所有文件上。有些情况下，分发文件中的发票不在所有文件上，而且在所有文件上都不在分支文件中。

我试图做的是在VBA中插入一个循环，以便在所有文件中的发票后自动发票，并打开特定的分支文件，然后在每个工作表中搜索发票编号。我还需要检查发行者是否相同，但是首先我尝试了这个代码，当它搜索该值时它返回错误的单元格！这是代码：

  Dim sh As Worksheet 
 Dim iLoop As Integer 
 For iLoop = 7 To 1719 
 
'这是发票在Excel表中的地方
 
 iloopoffset = iLoop  -  6 
 
'如上所示，列表发票从第7行开始，所以我用它来抵消
 
如果Range（K6）。Offset（iloopoffset）.Value =否然后
 
'列K是否在分支机构中找到发票的一个说法
 
设置searchingvalue = Range（B6）。Offset（iloopoffset，0）
 
'我用过这样我可以使用.find公式中的值
 
 MsgBox（searvalue.Value）
 Workbooks.Open（C：\Users\xxxxxx\Documents\xxxxxx\\ \\ XML+ Range（D6）。Offset（iloopoffset）.Value）
对于每个sh在工作表
如果ActiveSheet.Name =062015或052015或042015或032015 或022015或012015或122014或112014然后
 
 我需要这样做，因为在上面的名称的页面上，搜索的值将在另一列。 112014之前的纸张是不同的。 
 
设置NFE =工作表（sh.Name）.Range（B：B）。Find（Range（B6）。Offset（iloopoffset，0）.Value，lookat：= xlPart） 
 Else 
设置NFE = Worksheets（sh.Name）.Range（A：A）。Find（Range（B6）。Offset（iloopoffset，0）.Value，lookat：= xlPart ）
如果
 
 
如果不是NFE是没有
 MsgBox（在表单上找到+ ActiveSheet.Name ++ NFE.Address）
 Range（NFE.Address）.Offset（，12）.Value =YES
 
'对于
 
 ActiveWorkbook.Save 
 ActiveWindow.Close 
 End If 
 Next sh 
 ActiveWorkbook.Save 
 ActiveWindow.Close 
 End If 
 Next iLoop 
 End Sub 
  

发生了什么？我在VBA中是真正的noob，但是我没有发现这个代码有什么问题，你能帮助我吗？

解决方案

未验证：

  Sub test（）
 
 Const FILE_ROOT As String =C：\Users\xxxxxx\Documents\xxxxxx\XML
 Dim shtAll As Worksheet，rw As Range， searchvalue 
 Dim sh As Worksheet，wb As Workbook 
 Dim iLoop As Long，colSrch As Long，NFE As Range 
 Dim arrSheets 
 
 Set shtAll = ActiveWorkbook.Sheets 一切）'调整适合... 
 
'表要注意.... 
 arrSheets = Array（062015，052015，042015， 032015，022015，_ 
012015，122014，112014）
 
对于iLoop = 7至1719 
 
设置rw = shtAll .Rows（iLoop）
 
'如果没有找到... 
如果rw.Cells（1，K）。值=否然后
 
 searchvalue = rw.Cells（1，B）。值
 
设置wb = Workbooks.Open（FILE_ROOT& rw.Cells（1，D）。Value）
 
对于每个sh在wb.Worksheets 
 
'哪个列要搜索？检查工作表名称是否在arrSheets 
 colSrch = IIf（IsError（Application.Match（sh.Name，arrSheets，0）），1,2）
 
设置NFE = sh.Columns（ colSrch）.Find（searchingvalue，lookat：= xlPart）
 
如果不是NFE，那么
 MsgBox（在表单上找到+ ActiveSheet.Name ++ NFE.Address）
 NFE.Offset（，12）.Value =YES
 wb.Save 
退出
结束If 
 Next sh 
 
 wb 。关闭保存更改：= False 
结束如果
 
下一步iLoop 
 End Sub 
  

编辑

 如果不是NFE不是和Sh.Range（NFE ）.Offset（，8）= cnpj然后
  

我在这里看到的几个问题：

1. NFE 已经是一个范围，所以你可以做 NFE.Offset（，8）




2. VBA将始终评估和的部分，即使第一部分是False，所以在 NFE 是没有任何第二部分将导致运行时错误（因为您无法从Nothing Offset ...）。为了处理这个问题，你需要两个不同的If块：

    如果不是NFE，那么
   如果NFE.Offset（， 8）= cnpj然后
   '做某事
   如果
   结束If 
     

应该这样做。

I have a workbook containing a list of all invoices from all branches, let's call it "Everything", and basically I need to search if the invoices are found in another file containing each branch's invoices,. It's actually on file for each branch, and each file is divided with sheets by month, and I need to check in every sheet and then insert a value in a cell. Let's call this one "0001" and so on for each branch.

The "everything" file contains basically one column with the branch number, one with the invoice number, one with the issuer code and one saying if it was found on the branches files. The branches files contains the same except the branch number, and the last column says if the invoice is on the "Everything" file or not. There are cases where an invoice is on the branch file and is not on the "everything file" and also cases where it is on the everything file and is not on the branches file.

What I tried to do was insert a loop in VBA so it would go automatically invoice after invoice in the everything file and open the specific branch file, then search for the invoice number in each sheet. I would also need to check if the issuer is the same, but first I tried this code and when it searched for the value it returned the wrong cell! Here is the code:

Dim sh As Worksheet 
Dim iLoop As Integer 
For iLoop = 7 To 1719 

 ' this is where the invoices are in an excel sheet

iloopoffset = iLoop - 6 

 ' as you see above, the list of invoices starts at line 7, so I used this to offset

If Range("K6").Offset(iloopoffset).Value = "No" Then 

     ' Column K is the one saying if the invoice was found or not in the branches file

    Set searchedvalue = Range("B6").Offset(iloopoffset, 0) 

     ' I used this so i could use the value in the .find formula

    MsgBox (searchedvalue.Value) 
    Workbooks.Open ("C:\Users\xxxxxx\Documents\xxxxxx\XML " + Range("D6").Offset(iloopoffset).Value) 
    For Each sh In Worksheets 
        If ActiveSheet.Name = "062015" Or "052015" Or "042015" Or "032015" Or "022015" Or "012015" Or "122014" Or "112014" Then 

             ' I needed to do this because on the sheets with the names above, the searched value will be in another column. sheets before 112014 are different.

            Set NFE = Worksheets(sh.Name).Range("B:B").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart) 
        Else 
            Set NFE = Worksheets(sh.Name).Range("A:A").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart) 
        End If 


        If Not NFE Is Nothing Then 
            MsgBox ("Found on sheet " + ActiveSheet.Name + " " + NFE.Address) 
            Range(NFE.Address).Offset(, 12).Value = "YES" 

             ' yes for found

            ActiveWorkbook.Save 
            ActiveWindow.Close 
        End If 
    Next sh 
    ActiveWorkbook.Save 
    ActiveWindow.Close 
End If 
Next iLoop 
End Sub 

What is going on? I am a true noob in VBA, but i didn't find anything wrong with this code... can you help me?

解决方案

Untested:

Sub test()

    Const FILE_ROOT As String = "C:\Users\xxxxxx\Documents\xxxxxx\XML "
    Dim shtAll As Worksheet, rw As Range, searchedvalue
    Dim sh As Worksheet, wb As Workbook
    Dim iLoop As Long, colSrch As Long, NFE As Range
    Dim arrSheets

    Set shtAll = ActiveWorkbook.Sheets("Everything") 'adjust to suit...

    'sheets to watch out for....
    arrSheets = Array("062015", "052015", "042015", "032015", "022015", _
                      "012015", "122014", "112014")

    For iLoop = 7 To 1719

        Set rw = shtAll.Rows(iLoop)

        'if not found...
        If rw.Cells(1, "K").Value = "No" Then

            searchedvalue = rw.Cells(1, "B").Value

            Set wb = Workbooks.Open(FILE_ROOT & rw.Cells(1, "D").Value)

            For Each sh In wb.Worksheets

                'which column to search in? check if sheet name is in arrSheets
                colSrch = IIf(IsError(Application.Match(sh.Name, arrSheets, 0)), 1, 2)

                Set NFE = sh.Columns(colSrch).Find(searchedvalue, lookat:=xlPart)

                If Not NFE Is Nothing Then
                    MsgBox ("Found on sheet " + ActiveSheet.Name + " " + NFE.Address)
                    NFE.Offset(, 12).Value = "YES"
                    wb.Save
                    Exit For
                End If
            Next sh

            wb.Close savechanges:=False
        End If

    Next iLoop
End Sub

EDIT

If Not NFE Is Nothing And sh.Range(NFE).Offset(, 8) = cnpj Then

A couple of problem I see here:

1. NFE is already a Range, so you can just do NFE.Offset(,8)

2. VBA will always evaluate both parts of an And, even if the first part is False, so in cases where NFE is Nothing the second part will cause a run-time error (since you can't Offset from Nothing...). To handle this you need two distinct If blocks:

   If Not NFE Is Nothing Then
        If NFE.Offset(, 8) = cnpj Then
           'do something
        End If
   End If
   

Should do it.

这篇关于VBA找不到工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！