VBA每个单元格迭代搜索一个字符串 [英] VBA search each cell iteratively for a string

问题描述

我正在处理我的工作的设备数据库，我正在尝试在列的每个单元格中搜索一个变量字符串，一旦找到该字符串，我需要为另一个变量赋值在我正在搜索的列旁边的列中的单元格。
我可以执行循环和迭代，如果语句，但是我很难找到一个函数，允许我搜索字符串的特定单元格。

I'm working on equipment databases for my job, and I'm trying to search for a variable string in each cell of a column, and once that string is found, I need to assign another variable to the value of the cell in the column next to the column I'm searching. I can perform loops and iterations and if statements, however I'm having a hard time finding a function that would allow me to search specific cells for a string.

有没有办法指定单元格的搜索，像像单元格（x，y）.find（）等一样。还是不存在？此外，我已经在InStr（）函数中找到了很多文档，但这只能算出字符串在列中出现的次数，对吗？有什么办法可以返回行，找到字符串的单元格的col数吗？感谢您提供任何帮助。

Is there any way to specify a search of a cell, sort of like Cells(x,y).find()etc. or does that not exist? Also, I've found a lot of documentation on the InStr() function, but that only counts the number of times a string appears in a column, right? Is there any way to return the row,col numbers of cells that the string is found in? Thank you for any help you can offer.

Sub Trans()
Dim A As Range, r As Range, s As String

Set A = Range("A2:A11")

s = "T1"

For Each r In A
    If InStr(1, r.Value, s) > 0 Then
        i = r.Row
        Cells(i, C) = "here"
    End If

Next r

End Sub        

推荐答案

您是否正确使用 InStr ）。以下是搜索列的一个小例子：

You are correct about potentially using InStr(). Here is a tiny example of searching a column:

Sub SeekingHappiness()
    Dim A As Range, r As Range, s As String
    s = "happiness"
    Set A = Range("A1:A10")

    For Each r In A
        If InStr(1, r.Value, s) > 0 Then
            MsgBox r.Address & vbCrLf & r.Value
        End If
    Next r
End Sub

还有一个 .Find（）最小化循环的方法。

There is also a .Find() method that minimizes looping.

这篇关于VBA每个单元格迭代搜索一个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！