根据另一个单元格中的值查找并查找值

I am trying to build a macro that will search a specific column.

我正在尝试构建一个将搜索特定列的宏。

Here are the steps:
1. user enters a number into the cell and then executes the macro.
2. based on the value of what the user has entered, the macro will find the text in a column.

以下是步骤:1。用户在单元格中输入一个数字,然后执行宏。 2.根据用户输入的值,宏将在列中找到文本。

I got everything to work pretty well except I don't know how to define the value of the cell that the user enters. Any help here would be appreciated.

除了我不知道如何定义用户输入的单元格的值之外,我还能很好地工作。任何帮助在这里将不胜感激。

Sheets("New Version ").Select
    Range("B4").Select
    Sheets("PN_List").Select
    Columns("I:I").Select
    'below is where I struggle
    Selection.Find(What:=(""), After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

2 个解决方案

#1

Let's say the user enters a number into cell B4, then you just have to adjust your code into:

假设用户在单元格B4中输入一个数字,那么您只需将代码调整为:

Selection.Find(What:=Range("B4").Value, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

#2

You can do this in 2 ways.

您可以通过两种方式完成此操作。

Number1:

Module based: (code in module)

基于模块:(模块中的代码)

Sub Sample()

Dim search_range as Range, search_value as Range, _
    lastcell as Range, foundcell as Range
Dim ws as Worksheet

Set ws = Thisworkbook.Sheets("PN_List")
Set search_range = ws.Range("I1", ws.Range("I" & Rows.Count).End(xlUp))
Set lastcell = search_range.Cells(search_range.Cells.Count)
Set search_value = Thisworkbook.Sheets("New Version").Range("B4")

Set foundcell = search_range.Find(What:=search_value, After:=lastcell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

If Not foundcell Is Nothing Then foundcell.Activate Else Msgbox "Not Found"

End Sub

Number2:

Worksheet Event based. (code in Sheet)

基于工作表事件。 (Sheet中的代码)

Private Sub Worksheet_Change(ByVal Target as Range)

Dim search_range as Range, search_value as Range, _
    lastcell as Range, foundcell as Range
Dim ws as Worksheet

Set ws = Thisworkbook.Sheets("PN_List")
Set search_range = ws.Range("I1", ws.Range("I" & Rows.Count).End(xlUp))
Set lastcell = search_range.Cells(search_range.Cells.Count)
Set search_value = Thisworkbook.Sheets("New Version").Range("B4")

If Not Intersect(Target, search_value) Is Nothing Then
query = Msgbox("Search data?", vbYesNo)
If query = 7 Then Exit Sub
Set foundcell = search_range.Find(What:=search_value, After:=lastcell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

If Not foundcell Is Nothing Then foundcell.Activate Else Msgbox "Not Found"

End Sub

The first one you enter data in B4 then run the macro.
The second one fires every time you change value in B4.
A msgbox will appear asking if you want to search the data entered.

第一个在B4中输入数据然后运行宏。每次更改B4中的值时,第二个都会触发。将出现一个msgbox,询问您是否要搜索输入的数据。

Hope this helps.

希望这可以帮助。

#1