如何通过VBA代码搜索工作表？ [英] How to search on worksheet by VBA Code?

问题描述

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

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?

推荐答案

您将从意见中意识到请解决我的问题问题不受欢迎。

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.

转到Google并输入excel vba教程。你会被提供许多网站。他们都是不同的，所以尝试一些，找到一个适合你的。

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.

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

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

查看选项。例如，情况很重要？根据需要选择。我勾选匹配整个单元格内容，输入k并点击查找下一个。光标跳到包含k的单元格。我然后关闭了宏记录器。

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.

为我保存的代码是：

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

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

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.

关键变化是：

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


• 我们需要允许没有找到值。

将下面的宏复制到宏记录器保存其代码的模块。我通过修改保存的代码创建一个测试工具来创建这个宏，让你玩。它要求一个值，在列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.

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

现在再次打开宏录音机。将光标置于列A的下方，并带有值。点击 Ctrl + UpArrow 。光标将跳到列A中的最后一个值。关闭宏录音机。

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.

保存的代码将如下所示：

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）是VBA对于 Ctrl + UpArrow 。这是找到最后一行的最简单的方法。

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

要添加一个新行，如果找不到该值，您需要执行以下操作：

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"

如果您看其他问题，您将发现 End 有时不会给您期望的结果。在空列中尝试 Ctrl + DownArrow 和 Ctrl + UpArrow ，顶部有一个两个值的列，一列，底部有一个两个值，一列有多个值，由空行分隔。

If you look at other questions you will discover that End will sometimes not give you the result you expect. Try Ctrl+DownArrow and Ctrl+UpArrow on 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.

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

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

这篇关于如何通过VBA代码搜索工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！