vba问题与find和findnext [英] vba problems with find and findnext

问题描述

我想在第5列上搜索 BD 所有与一些名为 alocacao 在我的工作表 Plan1 。然后它应该将第2列上的值复制到名为 tecnico1 的单元格（其他单元格称为$ code> tecnico2，tecnico3和tecnico4 ）。我在下面说明：

值TESTE 2是 alocacao 。

我试图使用Find和FindNext，这是什么我尝试到目前为止：

  Sub VerifProd_Click（）
 
 Dim FoundCell As Range 
 Dim LastCell As Range 
 Dim FirstAddr As String 
 Dim fnd As String 
 Dim i As Long 
 
i = 2 
 fnd = Sheets（1）.Range alocacao）。值
 
带表格（BD）。列（5）
设置LastCell = .Cells（.Cells.Count）
结束
 
 Set FoundCell = Sheets（BD）。列（5）.Find（what：= fnd，after ：= LastCell）
 
如果没有找到Cell没有，然后
 FirstAddr = FoundCell.Address 
结束如果
 
直到找到Cell没有
表格（BD）。细胞（i，2）。复印单（Plan1）。范围（tecnico& i）.Value 
i = i + 1 
 Set FoundCell = Sheets（BD）。列（5）.FindNext（after：= FoundCell）
如果FoundCell.Address = FirstAddr Then 
退出Do 
结束如果
循环
 
结束子
  

但它不起作用，我得到运行时错误1004 ，但代码未突出显示。我不太熟悉Find和FindNext，所以我会感谢任何帮助，以了解为什么它不能正常工作。

编辑 p>

我正在尝试新的东西，我改变了一部分，只是为了测试它会将值粘贴到单元格B26上。现在我得到运行时错误438 ：

 使用表格（BD）。列（5）
设置LastCell = .Cells（.Cells.Count）
结束
 
设置FoundCell =表（BD）列（5）.Find什么：= fnd，after：= LastCell）
 
如果Not FoundCell没有，然后
 FirstAddr = FoundCell.Address 
如果
 
直到找到没有
表（Plan1）。Range（B26）= FoundCell.Adress.Offset（0，-3）.Value 
 
 Set FoundCell = Sheets（BD） .Columns（5）.FindNext（after：= FoundCell）
如果FoundCell.Address = FirstAddr然后
退出Do 
结束如果
循环
 Plan1
 p 
 
 code>名称为 tecnico1，tecnico2，tecnico3和tecnico4 ，我建议进行以下修改，请注意，我们应该停留在 4 匹配命名范围的数量 tecnico ：
  Sub VerifProd_Click（）
 Dim FoundCell As Range，FirstAddr As String，fnd As String，i As Long 
 
 fnd = Sheets（1）.Range（alocacao）。value 
 Set FoundCell = Sheets（BD）。列（5）.Find（what：= fnd，_ 
 After ：= Sheets（BD）。Cells（Rows.count，5），Lookat：= xlPart，_ 
 LookIn：= xlFormulas，SearchOrder：= xlByRows，SearchDirection：= xlNext）
 
如果FoundCell没有，然后退出Sub 
 Do 
i = i + 1 
表（Plan1）。范围（tecnico& i）.value = FoundCell.Offset（， -  3）.Value2 
 Set FoundCell = Sheets（BD）。列（5）.FindNext（After：= FoundCell）
循环直到FoundCell.Address = FirstAddr或i> = 4 
 End Sub 
  
 
I want to search on Column 5 on sheet BD all the entries that matches with some value called alocacao on my sheet Plan1. Then it should copy the value on Column 2 to the cell called tecnico1 (the other cells are called tecnico2, tecnico3 and tecnico4). I illustrate below:



The cell with the value TESTE 2 is the alocacao.





I tried to use Find and FindNext and this is what I tried so far:
Sub VerifProd_Click()

Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
Dim fnd As String
Dim i As Long

i = 2
fnd = Sheets(1).Range("alocacao").Value

With Sheets("BD").Columns(5)
    Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = Sheets("BD").Columns(5).Find(what:=fnd, after:=LastCell)

If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
End If

Do Until FoundCell Is Nothing
    Sheets("BD").Cells(i,2).Copy Sheets("Plan1").Range("tecnico" & i).Value
    i = i + 1
    Set FoundCell = Sheets("BD").Columns(5).FindNext(after:=FoundCell)
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
Loop

End Sub
But it doesn't work and I get Run-time error 1004 but the code is not highlighted. I'm not too familiar with Find and FindNext so I'll appreciate any help to understand why it's not working propperly.

EDIT

I was trying something new and I changed a part of it just to test it will paste the value on cell B26. Now I'm getting Run-time error 438:
With Sheets("BD").Columns(5)
    Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = Sheets("BD").Columns(5).Find(what:=fnd, after:=LastCell)

If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
End If

Do Until FoundCell Is Nothing
    Sheets("Plan1").Range("B26") = FoundCell.Adress.Offset(0, -3).Value

    Set FoundCell = Sheets("BD").Columns(5).FindNext(after:=FoundCell)
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
Loop

解决方案
Ok supposing you have 4 named cells in sheet "Plan1" with names tecnico1, tecnico2, tecnico3 and tecnico4, I suggest the following modification, having in mind that we should stop at 4 matches which the number of named ranges tecnico:
Sub VerifProd_Click()
    Dim FoundCell As Range, FirstAddr As String, fnd As String, i As Long

    fnd = Sheets(1).Range("alocacao").value
    Set FoundCell = Sheets("BD").Columns(5).Find(what:=fnd, _
        After:=Sheets("BD").Cells(Rows.count, 5), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If FoundCell Is Nothing Then Exit Sub
    Do
        i = i + 1
        Sheets("Plan1").Range("tecnico" & i).value = FoundCell.Offset(,-3).Value2
        Set FoundCell = Sheets("BD").Columns(5).FindNext(After:=FoundCell)
    Loop Until FoundCell.Address = FirstAddr Or i >= 4
End Sub


                        
这篇关于vba问题与find和findnext的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！