使用VBA在Excel中搜索单元格 [英] Search for a Cell in Excel using VBA

问题描述

问题:我想在单元格内容旁边或下方找到一个单元格的值，该值是工作簿的文本值.

Problem: I would like to find a value of a cell next to or below the cell content a text value for a workbook.

示例:在Sheet2中，我有两个随机放置的单元格(假设其索引是未知的，并且total并非定义的名称)

Example: In Sheet2, I have two cells stand random (assume its index is unknown and total is not a defined name)

我要搜索总计旁边的值"200"，并将其放在工作表2(活动单元格)中.如果有多个包含单词"Total"的单元格，请全部列出，并在可能的情况下，放入包含我要查找的单元格的工作表名称.值200张2

I want to search for the value "200" stands next to the total and put it in sheet 2 (an active cell). In case, there are multiple cell that contains the word "Total" list all of them and if possible, put the name of the sheet that contains the cell that I am looking for. Value 200 Sheet2

我的方法:1.用户输入

My Approach: 1. User input

1. 转到每个单元格并进行搜索.如果要在excel中搜索单元格的全部限制，这将花费一些时间.因此，搜索仅限制为100列x 10000行.

1. Go to each cell and search for it. This will take time if search for the whole limitation of cell in excel. So the search only limit to 100 columns x 10000 rows.

找到索引后，偏移1列以获得值

After find its index, offset to 1 columns to get the value

将结果写入ActiveCell.Value = Search_Value.然后继续搜索其余的工作表.偏移1个coloum和1行以写入第二个值...

Write the result ActiveCell.Value = Search_Value. Then continue to search for the rest of sheets. Offset 1 coloum and 1 row to write the second value...

搜索是一个非常困难的概念，我真的不知道如何进行搜索.请帮助

Searching is a very difficult concept, and I truly have no idea how to do the search part. Please help

推荐答案

With Worksheets(1).Range("a1:a500")
    counter=0
    Set c = .Find("Total", lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            counter=counter+1
            Worksheets(2).range("A1").offset(counter,0)=c.offset(0,1)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

firstaddress 保存找到的第一个单元格的位置(因此我们知道何时停止)； firstaddress.offset(0,1)将为您提供您要保存的值，因此设置 worksheet(2).range("a1").offset(counter，0)会列出在第二个标签上找到的所有值，从a1到范围内找到的所有值

firstaddress holds the location of the first cell found (so we know when to stop); firstaddress.offset(0,1) will give you the value you are trying to save, so setting worksheet(2).range("a1").offset(counter,0) will list all the values it finds on the 2nd tab, from a1 down to however many it finds in the range

这篇关于使用VBA在Excel中搜索单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！