如何通过 VBA 代码在工作表上搜索？

How to search on worksheet by VBA Code?

提问by Davuz

I have a worksheet with 2 columns "Key" and "Value". by VBA code, i want search Input_key on Key columns, if not exist, I will add new row [input-key]-[input-value]. How do i code?

我有一个包含 2 列“键”和“值”的工作表。通过 VBA 代码，我想在 Key 列上搜索 Input_key，如果不存在，我将添加新行 [input-key]-[input-value]。我如何编码？

回答by Tony Dallimore

You will realise from the comments that "please solve my problem for me" questions are not popular.

你会从评论中意识到“请帮我解决我的问题”问题并不流行。

I will guess that you do not know where to start and will give you some initial guidance.

我猜你不知道从哪里开始，会给你一些初步的指导。

Go to Google and type in "excel vba tutorial". You will be offered many sites. They are all different so try a few and find one that is right for you.

转到 Google 并输入“excel vba 教程”。您将获得许多网站。它们都是不同的，所以尝试一些并找到适合您的。

Try the macro recorder. I set up a worksheet which matches your description, switched on the macro recorder, selected column A, clicked Ctrl+Fto get the Find screen and clicked the option button to show me all the options. The result is:

试试宏记录器。我设置了一个与您的描述相匹配的工作表，打开宏记录器，选择 A 列，单击Ctrl+F以获取“查找”屏幕并单击选项按钮向我显示所有选项。结果是：

Look at the options. For example, is case important? Select as required. I ticked "match entire cell contents", entered "k" and clicked Find Next. The cursor jumped to the cell containing "k". I then switched the macro recorder off.

看看选项。例如，大小写重要吗？根据需要选择。我勾选了“匹配整个单元格内容”，输入“k”并单击Find Next。光标跳到包含“k”的单元格。然后我关闭了宏记录器。

The code saved for me was:

为我保存的代码是：

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/02/2012 by Tony Dallimore
'
  Columns("A:A").Select
  Selection.Find(What:="k", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False).Activate
End Sub

This is valid VBA but is not good VBA. The macro recorder has recorded each action as you performed it. It does not know your intentions. So we need to tidy this code up.

这是有效的 VBA，但不是好的 VBA。宏记录器记录了您执行的每个操作。它不知道你的意图。所以我们需要整理一下这段代码。

The key changes are:

主要变化是：

• We do not want to select column A or activate the cell containing the value found.
• We need to allow for the value not being found.

• 我们不想选择 A 列或激活包含找到的值的单元格。
• 我们需要允许找不到值。

Copy the macro below to the module in which the macro recorder saved its code. I created this macro by amending the saved code to create a test vehicle for you to play with. It asks for a value, searches for it in column A and says whether the value was found or not. This is the basis of the code you need.

将下面的宏复制到宏记录器保存其代码的模块中。我通过修改保存的代码来创建这个宏，以创建一个供您玩的测试车辆。它要求一个值，在 A 列中搜索它并说明是否找到了该值。这是您需要的代码的基础。

Sub PlayMacro()

  Dim Prompt As String
  Dim RetValue As String
  Dim Rng As Range
  Dim RowCrnt As Long

  Prompt = ""

  ' The macro recorder has used the active worksheet.  This says which
  ' worksheet is to be used whether it is active or not.  Change "Sheet4"
  ' to the name of your worksheet.
  With Sheets("Sheet4")

    ' This will loop forever unless a statement within
    ' the loop exits the Do.
    Do While True

      RetValue = InputBox(Prompt & "Give me a value to look for")
      'RetValue will be empty if you click cancel
      If RetValue = "" Then
        Exit Do
      End If

      ' I do not wish to active the cell containing the required value.
      ' I want to know where it is.
      Set Rng = .Columns("A:A").Find(What:=RetValue, After:=.Range("A1"), _
                LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

      If Rng Is Nothing Then
        ' The entered value could not be found
        Prompt = "I could not find """ & RetValue & """"
      Else
        ' The entered value was found
        RowCrnt = Rng.Row
        Prompt = "I found """ & RetValue & """ on row " & RowCrnt
      End If
      Prompt = Prompt & vbLf
    Loop

  End With

End Sub

Now switch the macro recorder on again. Position the cursor in column A below any rows with values. Click Ctrl+UpArrow. The cursor will jump to the last value in column A. Switch the macro recorder off.

现在再次打开宏记录器。将光标定位在具有值的任何行下方的 A 列中。单击Ctrl+UpArrow。光标将跳到 A 列中的最后一个值。关闭宏记录器。

The saved code will look like:

保存的代码将如下所示：

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 27/02/2012 by Tony Dallimore
'

'
    Range("A64").Select
    Selection.End(xlUp).Select
    Range("A28").Select
End Sub

End(xlUp)is the VBA for Ctrl+UpArrow. It is the easiest way of finding the last used row.

End(xlUp)是 VBA 的Ctrl+UpArrow。这是查找最后使用的行的最简单方法。

To add a new row, which you want to do if the value is not found:

添加一个新行，如果没有找到值，你想这样做：

RowCrnt = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(RowCrnt,1),Value = "Key"
.Cells(RowCrnt,2),Value = "Value"

If you look at other questions you will discover that Endwill sometimes not give you the result you expect. Try Ctrl+DownArrowand Ctrl+UpArrowon a empty column, a column with one then two values at the top, a column with one then two values at the bottom and a column with several values separated by blank rows.

如果您查看其他问题，您会发现这些问题End有时不会给您预期的结果。尝试Ctrl+DownArrow和Ctrl+UpArrow在空柱，用一个然后两个值的列在顶部，在底部一个然后两个值，并用几个值的列的列由空行分离。

This should get you started. Welcome to Excel programming. Good luck.

这应该让你开始。欢迎使用 Excel 编程。祝你好运。

回答by Excelhub Org

You can use following simple code with InStr command.

您可以在 InStr 命令中使用以下简单代码。

Private Sub CommandButton1_Click()
Dim RowNum As Long

RowNum = 1


Do Until Sheets("Data").Cells(RowNum, 1).Value = ""

If InStr(1, Sheets("Data").Cells(RowNum, 2).Value, TextBox1.Value, vbTextCompare) > 0 Then
On erro GoTo next1
ListBox1.AddItem Sheets("Data").Cells(RowNum, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheets("Data").Cells(RowNum, 2).Value
End If
next1:
RowNum = RowNum + 1
Loop
End Sub

you can read more and download file

您可以阅读更多内容并下载文件