如何在Excel中直接跳转到特定的单元格 [英] How to direct jump on specific Cell in Excel

问题描述

早上好/下午/晚上大家:)

Good Morning/Afternoon/Evening to all :)

我的第一次来这里....................:)

My First time on here so.................... :)

电子表格示例

我在条件格式设置"中使用此代码

I use this code in "Conditional Formatting"

=AND(ISNUMBER(SEARCH($B$3,A6)),$B$3<>)

所以我只能在屏幕截图上突出显示该单元格(每当在B3上键入 text 时，它就会自动在屏幕截图上突出显示包含单元格)，但我想要的是仅突出显示该单元格但不能跳转(选择)单元格，当我在搜索B3框中键入文本"时.

so i only able to highlight the cell as on Screenshot (Whenever type text on B3 it's automatically highlight the contain Cell as on Screenshot) but what I want is it's only highlight the cell but cannot jump (select) cell when i type ""text"" on Search B3 box.

例如:在屏幕截图上，我键入DW353并在A18单元号上以红色突出显示，但如果我键入其他文本为DW364、365，并且位于A24，A25上并继续向下，则仅突出显示特定的单元而且我必须搜索(上下滚动)以查找红色突出显示.我想要的是每当我在B3上键入文本"作为突出显示"并将包含单元格"上的直接跳转为(查找和替换)时.

For Example: On Screenshot I type DW353 and It's highlight in Redcolor on A18 Cell No. but if i type other text as DW364, 365 and on which located on A24, A25 and continue to down, it's only highlight the specific Cell and I have to Search (Scroll up and down) for look that RED Highlight. What i want is whenever i type a "Text" on B3 it's Highlight and direct Jump on Contain Cell as (Find and Replace).

在此先感谢您，很长的问题很抱歉:)

Thank you in Advance and Sorry for Long Question :)

推荐答案

完成这种功能的最简单方法是使用VBA触发高级筛选器"功能，以便隐藏所有其他行.

The easiest way to accomplish this kind of functionality is to use VBA to trigger the Advanced Filter functionality, so that all other rows are hidden.

方法如下:

1. 添加一个名为"MyList"的命名范围，该范围覆盖了要过滤的MACHINE NO:列中的整个数据范围. (最好将这个数据块转换为Excel表，然后仅引用MACHINE NO列，因为这将意味着命名范围是动态的，即，如果基础数据增长，它将自动进行调整.)
2. 在B2中输入文本"MACHINE NO:"
3. 添加一个名为标准"的命名范围，覆盖B2:B3
4. 添加一个名为输入"的命名范围，覆盖B3

这应该类似于以下内容:

This should look something like the below:

1. 将此代码放在与每台机器移动选项卡相对应的工作表模块中(而不是在标准代码模块中):

1. Put this code in the Sheet Module that corresponds to the Per Machine Movement tab (and NOT in a standard Code Module) :

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Input")) Is Nothing Then
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    Range("MyList").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
End If

End Sub

结束子

即像这样:

现在，只要有人输入机器编号，工作表就会被过滤以仅显示感兴趣的行:

Now, any time someone enters a Machine Number, the sheet will be filtered to show just the row of interest:

您可以在Daniel Ferry的出色博客中找到一个工作示例，该链接位于以下链接: http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html

You can find a working example at Daniel Ferry's excellent blog, at the following link: http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html

查找他在--- UPDATE ---

Look for the second sample file he posted under the heading --- UPDATE ---

根据您的后续问题，可以对这种方法进行调整以在多个列中进行搜索.首先，这是命名范围的设置(包括在输入单元格上方称为"Header"的新区域):

This approach can be tweaked to search across multiple columns, as per your follow-up question. First, here's the setup of the Named Ranges (including a new one above the input cell called "Header"):

...这是修改后的代码:

...and here's the amended code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim vRanges As Variant
Dim vItem As Variant

If Not Intersect(Target, Range("Input")) Is Nothing Then
    On Error GoTo errhandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlManual
    End With

    'Clear any existing filter
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0

    'Cycle through the search arrays one by one, and run the advanced filter until you find a match
    vRanges = Array("Range1", "Range2", "Range3", "Range4") '<<< Change these to match your range names

    For Each vItem In vRanges
        Range("Header") = Range(vItem).Cells(1)
        Range(vItem).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
        If Range(vItem).SpecialCells(xlCellTypeVisible).Count > 1 Then Exit For
    Next vItem
errhandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With

End If

End Sub

下面是几个屏幕快照，显示了它们在不同列上的匹配情况:

And here's a couple of screenshots showing it matching on different columns:

您可以设置单元格B2的格式，以便您无法看到它.

You can format cell B2 so that it can't be seen if you want.

我建议在设置"RangeX"命名范围之前，将工作簿中的不同输入区域转换为Excel表(也称为ListObjects).这是因为在本质上，表基本上是动态命名的范围，可以自动扩展以容纳新数据.因此，如果您随后手动设置指向表"列的命名范围"，则无需记住调整命名范围"以处理新数据，因为表"会自动为您执行此操作.参见下图:

I would suggest turning the different input areas in your workbook into Excel Tables (also known as ListObjects) before you set up the "RangeX" named ranges. That's because under the hood, Tables are basically dynamic named ranges that automatically expand to accommodate new data. So if you then manually set up a Named Range that points at a Table column, you never need to remember to adjust your Named Ranges in order to handle new data, because the Table automatically does this for you. See the image below:

请注意，您可以使用功能区中的表格样式"选项来更改表格的格式，甚至可以完全关闭格式:

Note that you can change the formatting of Tables using the Table Styles option in the ribbon, or even turn off the formatting entirely:

这篇关于如何在Excel中直接跳转到特定的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！