搜索工作表中有三个名字 [英] Search worksheet for three names

问题描述

而不是查找大于6的数字，并将其发送到另一张表。我想查找3个名字，所以我可以搜索一个联系人列表，并把它们的信息从工作表拉到报表。

以下是我的旧代码：

  Private Sub CommandButton1_Click（） 
 
 Dim ws As Worksheet，myCounter 
 Dim erow，myValue As Long 
 
对于每个ws In Sheets 
如果ws.Range（C3） 。价值> 6然后
 myCounter = 1 
 ws.Select 
 ws.Range（c3）。选择
 
 myValue = ws.Range（C3）。 
工作表（报告）。选择
 erow = ActiveSheet.Cells（Rows.Count，1）.End（xlUp）.Offset（1，0）.Row 
 ActiveSheet.Cells erow，1）= myValue 
 
 nextValue = MsgBox（在& ws.Name& Chr（10）中找到的值和Continue？，vbInformation + vbYesvbNo，ws.Name& C3 =& ws.Range（C3）。值）
选择案例nextValue 
案例Is = vbYes 
案例Is = vbNo 
退出子
结束选择
结束如果
下一个ws 
 
如果myCounter = 0然后
 MsgBox没有一个表包含一个& Chr（10）& value in 6 in cell C3，vbInformation，Not Found
 End If 
 
 End Sub 
  

我认为第三行应该是 String 而不是 Long 。 / p>

我正在找的名字是DavidAndrea& Caroline，不知道我写了三次，还是用一个循环。此外，我无法弄清楚如何在整个电子表格中搜索这些名称。

解决方案

下面的代码将会查找在所有工作表中，单元格C3中的名称为David，Andrea和Caroline。对于每个匹配，它将它复制到报告工作表中的列A中的第一个空行。

注意：没有必要使用选择和 ActiveSheet ，而是使用完全有条件的单元格和工作表。

代码

  Option Explicit 
 
 Private Sub CommandButton1_Click（）
 
 Dim ws As Worksheet，myCounter As Long 
 Dim erow As Long，myValue As Long 
 Dim nextValue As Long 
 
对于每个ws在ThisWorkbook.Sheets 
与ws 
选择案例.Range（C3）。值
案例David，Andrea，Caroline
 myCounter = 1'raise flag>>发现在至少1张
 
'在报告表中获得第一个空行
 erow = Worksheets（报告）。单元格（工作表（报告）。Rows.Count， 1）.End（xlUp）.Offset（1，0）.Row 
工作表（报告）。单元格（erow，1）= .Range（C3）值
 
 nextValue = MsgBox（在& .Name& Chr（10）和Continue？中找到的值，vbInformation + vbYesNo，.Name&C3 =& .Range（C3） ）
选择案例nextValue 
案例Is = vbYes'<  - 如果你没有在这里做任何事情，你不需要>>也许你不需要整个选择案例这里
案例Is = vbNo 
退出子
结束选择
结束选择'选择案例.Range（C3）。价值
结束
下一个ws 
 
如果我的Counter = 0然后
 MsgBox没有一张表包含名称& Chr（10）& 'David'，'Andrea'，'Caroline'in cell C3，vbInformation，Not Found
 End If 
 
 End Sub 
  

---

评论：似乎你没有做任何事情， code> Case is = vbYes in the Select Case below：

  nextValue = MsgBox（Value in found& .Name& Chr（10）&Continue？，vbInformation + vbYesNo，.Name&C3 =&范围（C3）。价值）
选择案例nextValue 
案例Is = vbYes'<  - 如果你没有在这里做任何事情，你不需要>>也许你不需要整个选择案例这里
案例Is = vbNo 
退出子
结束选择
  

您可以用以下方式替换整个事物：

 如果MsgBox（Value found在& .Name& Chr（10）&Continue？中，vbInformation + vbYesNo，.Name&C3 =& .Range（C3）。Value）= vbNo Then 
退出Sub 
结束如果
  

Instead of looking for a number greater than 6 and sending it to another sheet. I want to look up 3 names so I can search a contact list and have it pull their information from the sheet to the report sheet.

below is my old code:

Private Sub CommandButton1_Click()

Dim ws As Worksheet, myCounter
Dim erow, myValue As Long

For Each ws In Sheets
    If ws.Range("C3").Value > 6 Then
        myCounter = 1
        ws.Select
        ws.Range("c3").Select

        myValue = ws.Range("C3").Value
        Worksheets("Report").Select
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(erow, 1) = myValue

        nextValue = MsgBox("Value found in " & ws.Name & Chr(10) & "Continue?", vbInformation + vbYesvbNo, ws.Name & " C3 = " & ws.Range("C3").Value)
        Select Case nextValue
            Case Is = vbYes
            Case Is = vbNo
                Exit Sub
        End Select
    End If
Next ws

If myCounter = 0 Then
    MsgBox "None of the sheets contains a " & Chr(10) & "value greater than 6 in cell C3 ", vbInformation, "Not Found"
End If

End Sub

I think the third row should be String instead of Long.

The names I'm looking for are "David" "Andrea" & "Caroline", not sure if I write it three times or use a loop. Also I can't figure out how to search in the entire spreadsheet for these names.

解决方案

The code below will look for the names "David", "Andrea" and "Caroline" in cell "C3" in all of the worksheets. For every match it will copy it to the first empty row in Column A in "Report" worksheet.

Note: There is no need to use Select and ActiveSheet, instead use fully qualifed Cells and Worksheets.

Code

Option Explicit

Private Sub CommandButton1_Click()

Dim ws As Worksheet, myCounter As Long
Dim erow As Long, myValue As Long
Dim nextValue As Long

For Each ws In ThisWorkbook.Sheets
    With ws
        Select Case .Range("C3").Value
            Case "David", "Andrea", "Caroline"
                myCounter = 1 ' raise flag >> found in at least 1 sheet

                ' get first empty row in "Report" sheet
                erow = Worksheets("Report").Cells(Worksheets("Report").Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                Worksheets("Report").Cells(erow, 1) = .Range("C3").Value

                nextValue = MsgBox("Value found in " & .Name & Chr(10) & "Continue?", vbInformation + vbYesNo, .Name & " C3 = " & .Range("C3").Value)
                Select Case nextValue
                    Case Is = vbYes ' <-- if you are not doing anything here, you don't need it >> maybe you don't need the entire `Select Case` here
                    Case Is = vbNo
                        Exit Sub
                End Select
        End Select ' Select Case .Range("C3").Value
    End With
Next ws

If myCounter = 0 Then
    MsgBox "None of the sheets contains the names " & Chr(10) & " 'David', 'Andrea', 'Caroline' in cell C3 ", vbInformation, "Not Found"
End If

End Sub

---

Comment: It seems you are not doing anything in the case of Case Is = vbYes in the Select Case below:

nextValue = MsgBox("Value found in " & .Name & Chr(10) & "Continue?", vbInformation + vbYesNo, .Name & " C3 = " & .Range("C3").Value)
Select Case nextValue
    Case Is = vbYes ' <-- if you are not doing anything here, you don't need it >> maybe you don't need the entire `Select Case` here
    Case Is = vbNo
        Exit Sub
End Select

You can replace the entire thing with :

If MsgBox("Value found in " & .Name & Chr(10) & "Continue?", vbInformation + vbYesNo, .Name & " C3 = " & .Range("C3").Value) = vbNo Then
    Exit Sub
End If

这篇关于搜索工作表中有三个名字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！